运维 之 神通数据库

isql -dosrdb -p2003 -Usysdba/szoscar55

进入后台：oscar/kstore
    列出库：list database
    删除库: drop database osrdb       
    创建库：create database osrdb [encoding 'GBK']
标准化库：oscar -o restrict -d osrdb
安装库：oscar -o install -d osrdb
卸载库：oscar -o uninstall -d osrdb

创建用户：create user test_usr with password 'test' default tablespace TS_USER_YS_USR_DATA role sysdba;
变更密码：alter user test_usr with password 'test_change';

create role ROLE_ALL_SELECT
grant select on sys_class to role ROLE_ALL_SELECT;
select 'grant create on schema ' || TABLE_SCHEM || ' to role  ROLE_ALL_SELECT;' from (select distinct TABLE_SCHEM from v_sys_tables where  TABLE_SCHEM  not in ('INFO_SCHEM','SYSDBA','STAGENT'));
select 'grant select on ' || TABLE_NAME || ' to role  ROLE_ALL_SELECT;' from (select TABLE_SCHEM|| '.' ||table_name as table_name from v_sys_tables where  TABLE_SCHEM not in ('INFO_SCHEM','SYSDBA','STAGENT'));

select TABLE_NAME,REMARKS from V_SYS_TABLES where table_schem='' order by TABLE_NAME
select TABLE_NAME,COLUMN_NAME,TYPE_NAME,COLUMN_SIZE,REMARKS,IS_NULLABLE from V_SYS_COLUMNS where table_name=''

oscar --version                 产看数据库版本
oscar –o normal –d osrdb        正常模式启动OSRDB数据库
oscar –o install –d osrdb            安装数据库实例OSRDB的服务
oscar –o uninstall –d osrdb        卸载数据库实例OSRDB的服务
oscar –o uninstall all            卸载所有数据库实例的服务
oscar –h d:\ShenTong –o normal –d osrdb        设定数据库安装目录并正常启动
oscar –o normal –d osrdb –P 2004        在2004端口启动数据库实例
oscar –e “create database osrdb”    执行创建数据库OSRDB实例的命令


oscaragent –v                    查看agent版本号
oscaragent –c                    命令行方式启动
oscaragent –r                    以服务方式启动(windows)
oscaragent –e d:\ShenTong –c    设定数据库安装目录并启动
oscaragent –s                    关闭agent（通过socket向agent发送退出信号）
oscaragent –S                    关闭agent服务
oscaragent –S –A                停止所有数据库服务
oscaragent –S –D <DBNAME>    停止指定的数据库实例服务
oscaragent –Q                    查询agent的服务运行状态
oscaragent –Q -A                查询所有数据库实例服务的运行状态
oscaragent –Q –D <DBNAME>    查询指定的数据库实例服务运行状态
oscaragent –i                    安装agent系统服务（跨平台）
oscaragent –u                    卸载agent系统服务（跨平台）
oscaragent –h/-?                帮助

SELECT T.TSNAME AS TSNAME,
       D.PATH,
       COUNT(*) "File Count",
       SUM(D.SIZE)  1048576 AS "Size(MB)",
       SUM(D.FREESIZE)  1048576 AS "Free Size(MB)",
       (SUM(D.SIZE) - SUM(D.FREESIZE))  1048576 AS "Used Size(MB)"
FROM SYS_TABLESPACE T
LEFT JOIN V_SYS_DATAFILE_INFO D
    ON T.TSID = D.TABLESPACEID
GROUP BY T.TSNAME,D.PATH;

execute direct on all 'SELECT T.TSNAME AS Schema, SUM(D.SIZE)  1048576 AS "Size(MB)",  SUM(D.FREESIZE)  1048576 AS "Free Size(MB)",  (SUM(D.SIZE) - SUM(D.FREESIZE))  1048576 AS "Used Size(MB)"   FROM SYS_TABLESPACE T,V_SYS_DATAFILE_INFO D where T.TSID = D.TABLESPACEID  and T.TSNAME=''STLTS'' GROUP BY T.TSNAME';

SELECT T.TSNAME as "NAME",
       PATH,
       "CREATIONTIME" as "Creation Time",
       "NEXTSIZE"  1048576 as "Next Size(MB)",
       "MAXSIZE"  1048576 AS "Max Size(MB)",
       "SIZE"  1048576 AS "Size(MB)",
       FREESIZE  1048576 AS "Free Size(MB)",
       ("SIZE" - FREESIZE)  1048576 AS "Used Size(MB)",
       ("SIZE" - FREESIZE) * 100.0  SIZE AS "Used RATIO"
FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T
WHERE T.TSID = TABLESPACEID;

SELECT T.TSNAME as "NAME",
       PATH,
       "CREATIONTIME" as "Creation Time",
       "NEXTSIZE"  1048576 as "Next Size(MB)",
       "MAXSIZE"  1048576 AS "Max Size(MB)",
       "SIZE"  1048576 AS "Size(MB)",
       FREESIZE  1048576 AS "Free Size(MB)",
       ("SIZE" - FREESIZE)  1048576 AS "Used Size(MB)",
       ("SIZE" - FREESIZE) * 100.0  SIZE AS "Used RATIO"
FROM V_SYS_DATAFILE_INFO, SYS_TABLESPACE T
WHERE T.TSID = TABLESPACEID;

SELECT PATH,
       "CREATIONTIME" as "Creation Time",
       "INITSIZE"  1048576 as "Init Size(MB)",
       "REALSIZE"  1048576 as "Real Size(MB)",
       "NEXTSIZE"  1048576 as "Next Size(MB)",
       "MAXSIZE"  1048576 AS "Max Size(MB)",
       "ISACTIVE" AS "Is Active)",
       ("REALSIZE" * (100-"USAGERATIO")/100)  1048576 AS "Free Size(MB)",
       ("REALSIZE" * "USAGERATIO"/100)  1048576 AS "Used Size(MB)",
       "USAGERATIO" AS "Used RATIO"
FROM V_SYS_LOGFILE_INFO;

select u.USENAME, c.relname, s.size, ts.tsname 
from sys_class c, v_segment_info s, sys_tablespace ts, v_sys_user u
where c.oid = s.relid 
      and c.relname='MM_T_GW_NORATE_17_201107'
      and s.fileid = ts.tsid 
      and u.USESYSID = c.RELOWNER
      and u.USENAME='TWBTEST'
order by s.size;

select * from  V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_UE_17_201109';

select sum(SIZE) from  V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_NORATE_17_201109';

select "SESSION ID", "APPNAME", "CURRENT SQL", "LAST SQL"  from v_sys_sessions where "LOGON USER" != 'INVALID USER';

select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID;
select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"<>'java.exe';
select w.SESSION_ID,w.EVENT_NAME, s."SESSION ID", s."APPNAME", s."MACHINE", s."LOGONTIME", s."TOTAL PORTAL", s."SQL COUNT", s."CURRENT SQL", s."LAST SQL" from v_sys_sessions s, V$SESSION_WAIT w where s."SESSION ID"=w.SESSION_ID and s."APPNAME"='insert_db';

select * from V$WAIT_CHAINS;
--kill掉session id
kill session 631 abort;


alter tablespace stlts default nologging;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select b.called_number || ',' ||  sum(a.total_due_amount)/1000 from t_sms_13_201202 a,call b where a.called_number=b.CALLING_NUMBER and record_type=0 group by b.called_number;

select SYS_GET_TABLEDEF from v_sys_table where tablename='XXX';


select SYS_GET_TABLEDEF((SELECT OID from sys_class where relname='TEST_02'));

create table test(tc1 int) partition by hash(tc1) partitions 4;
explain select * from test;

--根据分区键值查询
select count(*) from bill partition for('2010-01-01');
--根据分区名查询
select * from t1 partition(分区名);

查询相关表的试图
select * from v_sys_views where DEFINITION like '%T_GW_NORATE_88_201112%' 
select c.relname,d.* from sys_depend d,sys_class c where d.OBJID=c.oid

SELECT * FROM V_SYS_DATABASE_INFO;

select r.relname, r.REFOBJID, c2.relname "REF RelName", r.OBJID "REF OID" from (select c.relname relname,d.* from sys_depend d,sys_class c where d.REFOBJID=c.oid and c.relname='T_GW_UE_18_201204') r, sys_class c2 where c2.oid=(r.OBJID::integer-1);

select s.prev_sql, 'kill session ' || s.sid || ' abort ;', w.pid, w.BLOCKER_PID from v$wait_chains w, v$session s where BLOCKER_PID is null and s.sid = w.SESSION_ID;

select defaulttablespace as "default tablespace" from sys_shadow where usename='SYSDBA';


select SID from V$SESSION where LOGON_USER<>'' and CURRENT_SQL ='' and PREV_SQL_EXEC_START<=now()::timestamp-1/48;


select BUFFERS*8/1024 total,FREE_BUFFERS*8/1024 free,DIRTY_BUFFERS*8/1024 dirty from V$BUFFER_STATISTICS;

execute direct on all 'select * from V_SYS_IND_SUBPARTITIONS where IDXUSABLE=''f''';

select d.NSPNAME || '.' || c.relname,a.* from v_sys_tab_partitions a,sys_tabpart b , sys_class c,SYS_NAMESPACE d where a.oid = b.oid and b.BO=c.oid and c.RELNAMESPACE = d.oid;

CREATE DATABASE "HNBILL" NOARCHIVELOG LOGFILE 'HNBILL01.log' SIZE 10G AUTOEXTEND ON NEXT 100M CONTROLFILE “/OSCAR/admin/test.ctrl” DATAFILE 'HNBILL01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M;

CREATE TABLESPACE DR_G_A DATAFILE 'DR_G_A.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M;

CREATE USER stl_usr WITH DEFAULT TABLESPACE stlts PASSWORD 'stl_usr' ROLE SYSDBA;


drop user dba_usr;
CREATE USER dba_usr WITH PASSWORD 'dba_usr' ;

drop user  JX_DW cascade;

ALTER USER SYSDBA WITH PASSWORD 'UNIcom00';

ALTER DATABASE stlcdr ADD LOGFILE '/stl_db/db_data/stdb/odbs/STLCDR/STLCDR00.log' SIZE 50G AUTOEXTEND ON NEXT 1G;


ALTER DATABASE RENAME FILE ‘/diska/logs/log1a.log’ TO ‘/diskc/logs/log1c.log’;

select * from V_SYS_ARCHIVELOG_INFO;


ALTER DATABASE ARCHIVELOG ‘/OSCAR/arch/’;(归档，归档路径为/OSCAR/arch/)


ALTER DATABASE NOARCHIVELOG;(不归档)

ALTER DATABASE "OSCAR" datafile '/kstore1/KSTORE/odbs/KSTORE/KSTORE06.dbf' AUTOEXTEND ON MAX 700G;

create sequence SEQ_UP_MONI_COL_ID minvalue 1 maxvalue 1111111111111 start with 132 increment by 1 cache 20;


select nextval('SEQ_UP_MONI_COL_ID');
SELECT currval('SEQ_UP_MONI_COL_ID');
select * from SEQ_UP_MONI_COL_ID;

select rowid, ROAM_TYPE from T_GPRS_UE_84_201203 where char_length(ROAM_TYPE) > 4  limit 1;

ALTER TABLE maininfoall_test SPLIT PARTITION PART_MAX at (1341187200) INTO ( PARTITION PART_MAX, PARTITION PART_20120701 TABLESPACE TS_MAININFO_TEST_20120701) update indexes;


ALTER TABLE MB_SSJC SPLIT PARTITION PART_MB_SSJC_MAX AT ('2012-12-07 00:00:00') INTO (PARTITION PART_MB_SSJC_20121206 TABLESPACE TS_MB_SSJC_20121206 INIT 1024K NEXT 1024K, PARTITION PART_MB_SSJC_MAX TABLESPACE TS_MB_SSJC_MAX INIT 10240K NEXT 10240K) QUICK TO right;


DUMP：
alter session set events(immediate trace name errorstack);

brcmd -O backup -d HNBILL -t full -k file  -f "/kstore/sttmp/test_T.osrbk" -c TRUE

usage:
brcmd -O OpertionType -d DatabaseName -u Username -p Password -P OscarPort -t BRType -k DataTransType -f BKFileName -R RetainDays[ -e brcmdLogPath | -b BufferSize | -D BackupDesc | -c Compress | -m ManualDelete | -T TimePoint | -s RSDataFilePath | -l RSLogFilePath | -B B_CFG_BUF_DATA_BUFFER_PAGES/1024 | -v | -h];


brcmd -O restore -d HNBAK -u KSTORE -p kstore -P 2003 -t backup_point_last -k file -f "/kstore/sttmp/test.osrbk" -b 8192 -e "/kstore/sttmp/brcmd_log_021509.txt"

-O     Operatin类型的参数：backup/restore/delete_backup/query_db_verify_code  
                 备份/恢复/删除备份/查询数据库验证代码
-f        备份文件的完整路径（管道）
-d        数据库的名称，注意大写和小写字母,默认值是OSRDB
-u        数据库的用户名,默认值是SYSDBA
-p        用户名的密码, 默认值是szoscar55
-P        数据库服务的监听端口，默认值是2003
-k        数据传输类型：file/pipe，文件/管道，默认值是文件
-e        brcmd的日志路径， 默认值是oscaragent的日志路径
-b        数据传输缓冲区大小（KB）,默认数量为4096，最大数量是8192

-t        备份类型：full/incr/diff,完全/增量/差异
-S        备份阶段：all/df/lsn/al
-D        备份描述,默认值是null
-c        是压缩备份文件？默认值是false
-m        是永远保持备份文件？默认值是false
-A        是自动将数据库设置为归档模式时，你会做一个在线备份，但数据库正在运行无存档模式吗？默认值是false
-n        是没有在线备份归档日志？默认值是false

-t        还原类型：backup_point/backup_point_last/full/full_last/full_last_ignore_error/time_point/time_point_last
                备份点/最后一次备份点/完全/上一次完全/上一次完全忽略错误/时间点/最后一次时间点
-T        将数据库恢复到某一时间点，时间格式是YYYYMMDD_HHMMSS，只有当使用了-t参数是time_point/time_point_last
-s        还原数据库的数据文件路径，只有当使用了-t参数是backup_point / backup_point_last
-l        恢复数据库的日志文件的路径，只有当使用了-t参数是backup_point / backup_point_last
-a        恢复拱数据库的日志文件路径,只有当使用了-t参数是backup_point / backup_point_last
-B        DB_CFG_BUF_DATA_BUFFER_PAGES参数的值,默认值是8192，数据缓冲区的实际大小是64MB,操作系统的虚拟内存应该超过此值
-F        还原参数的XML文件的路径
-v        显示版本
-?        显示帮助

osrexp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=full file=d:/backup log=d:/bk.log mode=entirety ignore=false view=truesequence=true procedure=true constraint=true trigger=true index=true

osrexp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=schema file=d:/backup log=d:/bk.log mode=entirety ignore=false schema=(sysdba,public) excludetable=(public.test,sysdba.test) view=true sequence=true procedure=true constraint=true trigger=true index=true

osrexp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=table file=d:/backup log=d:/bk.log mode=entirety ignore=falsetable=(messagebord.test,sysdba.test) view=false procedure=false sequence=false constraint=true trigger=true index=true

[LOG]: -h                               服务器名
[LOG]: -p                               服务器端口
[LOG]: -u                               用户名和密码
[LOG]: -k                               表示以操作系统用户登录
[LOG]: -d                               数据库名称
[LOG]: -v                               显示逻辑备份工具、anywhere组件，及JDBC驱动的版本信息
[LOG]: level                            备份级别
[LOG]: parfile                          指定一个文件名,文件包含备份操作参数的列表
[LOG]: file                             指定备份的文件
[LOG]: log                              指定备份的日志
[LOG]: mode                             备份方式
[LOG]: schema                           模式级别下要备份的模式
[LOG]: table                            表级别下要备份的表对象
[LOG]: view                             是否备份视图
[LOG]: sequence                         是否备份序列
[LOG]: procedure                        是否备份存储过程
[LOG]: constraint                       是否备份表约束
[LOG]: trigger                          是否备份表触发器
[LOG]: index                            是否备份表索引
[LOG]: synonym                          是否备份同义词
[LOG]: package                          是否备份包
[LOG]: type                             是否备份自定义类型
[LOG]: grants                           是否备份对象权限
[LOG]: markLogging                      备份表时，是否记录日志模式
[LOG]: excludetable                     指定不需要备份的表，对模式级别和full级别有效
[LOG]: createtableifneo                 恢复时如果备份文件中的某张表在目标模式中不存在，是否重新创建表
[LOG]: query                            备份时，表对象的查询条件，只在表级别生效
[LOG]: excel                            是否导出excel文件
[LOG]: onlyExcel                        是否只备份或恢复excel文件
[LOG]: printdetail                      是否打印详细信息
[LOG]: printExcp                        是否打印异常信息的错误堆栈
[LOG]: ignore                           是否忽略不重要的错误
[LOG]: nameSensitive                    对象大小写是否敏感
[LOG]: encrypted                        密钥加密：encrypted=true|false SM4加密：encrypted=ecb|cbc
[LOG]: compress                         是否压缩备份文件
[LOG]: merge                            是否合并数据文件
[LOG]: tc                               指定备份表数据线程数
[LOG]: shardingCount                    指定表分片数，行数小于阈值时不分片
[LOG]: batchSize                        备份时就是fetchsize，恢复时如果设置大于0,按行提交数据而不是buffer
[LOG]: scn                              指定当前SCN值
[LOG]: flashbackUseArcLog               是否使用归档日志进行闪回查询
[LOG]: fsc                              是否从v_sys_class中获取表行数

osrimp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=full file=d:/backup log=d:/rt.log mode=entirety ignore=y recreateschema=falseview=true sequence=true procedure=true recreateotherobject=truerecreatetable=true constraint=true deletetabledata=true trigger=trueindex=true

osrimp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=schema file=d:/backup log=d:/rt.log mode=entirety ignore=trueschema=(sysdba,public) recreateschema=false view=true sequence=trueprocedure=true recreateotherobject=true excludetable=(sysdba.test,public.test) recreatetable=falseconstraint=false deletetabledata=true trigger=false index=false

osrimp -usysdba/szoscar55 -hlocalhost -p2003 -dosrdb level=table file=d:/backup log=d:/rt.log mode=entirety ignore=truerecreateschema=false view=false procedure=false sequence=truerecreateotherobject=false table=(sysdba.test,public.test)recreatetable=true constraint=true deletetabledata=true trigger=trueindex=true