神通数据库建库建用户迁移常用SQL/锁等待排查

select convert(char(19),getdate(),20);


#!/bin/bash

isql -Usysdba/szoscar55 -p 2005 -d test3 <
select table_name from all_tables where owner='XXXXX';

SQL

创建用户
create user XXXXXwith password 'XXXX' role sysdba default tablespace NNC_DATA01;


7. 检查数据库是否存在堵塞/锁问题：
SELECT
C.SESSION_ID,
C.BLOCKER_IS_VALID,
C.WAIT_EVENT,
C.BLOCKER_PID,
C.BLOCKER_SESSION_ID,
S.SQL_EXEC_START,
S.SQL_EXEC_START::TIMESTAMP - SYSDATE::TIMESTAMP "EXPEND TIME",
SUBSTR(
S.CURRENT_SQL,
0,
60
) CURRENT_SQL,
SUBSTR(
S.PREV_SQL,
0,
60
) PREV_SQL
FROM
V$SESSION S,
V$WAIT_CHAINS C
WHERE
C.SESSION_ID = S.SID
ORDER BY
BLOCKER_IS_VALID;


--查看当前库，所有的等待链关系-锁
SELECT
PATH AS "阻塞链",
SESSION_ID AS "我的会话ID",
BLOCKER_SESSION_ID AS "我等待的会话ID",
BLOCKER_TXN_ID AS "我等待的会话事务号",
WAIT_EVENT AS "等待的资源",
P1_TEXT,
P1,
P2_TEXT,
P2,
P3_TEXT,
P3,
NUM_WAITERS AS "等待我的会话总数"
FROM
(
SELECT
LEVEL,
SYS_CONNECT_BY_PATH(
SESSION_ID,
'--'
) AS PATH,
*
FROM
V$WAIT_CHAINS START WITH BLOCKER_SESSION_ID IS NULL CONNECT BY PRIOR SESSION_ID = BLOCKER_SESSION_ID
)
ORDER BY
LEVEL,
PATH;








收集整库的统计信息
exec dbms_utility.analyze_database('compute');



诊断查询时间最长时间SQL,在sql交互式工具执行下列语句可跟踪耗时长100语句：
执行一次这两个sql
SET TOP_COST_SQL_LIST_LEN =0;
SET TOP_COST_SQL_LIST_LEN =100;
重复执行下列语句收集一段时间内执行时间最长SQL语句
SELECT "TIME(s)", SQL FROM V_SYS_TOP_COST_SQLS ORDER BY "TIME(s)" desc limit 100;








select tbspace from syscat.tablespaces where tbspace in ('NNC_DATA01') or tbspace in ('NNC_INDEX01');





断开会话

kill session 会话ID    ---尝试中断事务
kill session 会话ID abort  ---尝试断开连接

-----
查看旧库的表空间
isql -Usysdba/szoscar55 -d osrdb -p 2003
select * from sys_tablespace;
逻辑备份

---将从Oracle迁移的库test3进行逻辑备份
./osrexp -usysdba/szoscar55 -p2005 -dtest3 level=full file=/data/backup/test3full.osr logfile=/data/backup/test3full.log




---安装342.81版本数据库并创建test2,test3实例
安装目录 /data/ShenTong
改为/data/ShenTong2/
改为/data/ShenTong/
test2 port 2004
create database test2 encoding 'GBK' logfile'TEST201.log' size 1G datafile 'TEST201.dbf' size 1G autoextend on next 200m DATEFORMAT 'ORACLE';
oscar -o restrict -d test2
oscar -o install -d test2

create tablespace NNC_DATA01 datafile 'NNC_DATA01.dbf' size 1G autoextend on next 200m;
create tablespace NNC_INDEX01 datafile'NNC_INDEX01.dbf' size 1G autoextend on next 200m;

create user xxxxwith password 'xxxxx' role sysdba default tablespace NNC_DATA01;
./osrimp -usysdba/szoscar55 -p2004 -dtest2 file=/st/backup/xxxx.osr logfile=/st/backup/xxxx.log fromuser=xxxxx touser=xxxxxlevel=schema ignore=true



test3 port 2005
将Oracle数据库迁移至AA2实例
Oracle数据库：xxxx/1@xxxxxx:1521/orcl
create database test3 encoding 'UTF8' logfile'TEST301.log' size 1G datafile 'TEST301.dbf' size 1G autoextend on next 200m DATEFORMAT 'ORACLE';
oscar -o restrict -d test3
oscar -o install -d test3

create user xxxxx with password 'xxxxxxx';
USERS
NNC_DATA01
NNC_INDEX01
CREATE TABLESPACE "USERS" DATAFILE 'USERS01.dbf' SIZE 1g AUTOEXTEND ON NEXT 200m;
CREATE TABLESPACE "NNC_DATA01" DATAFILE 'NNC_DATA01.dbf' SIZE 5g AUTOEXTEND ON NEXT 300m;
CREATE TABLESPACE "NNC_INDEX01" DATAFILE 'NNC_INDEX01.dbf' SIZE 5g AUTOEXTEND ON NEXT 300m;
空库：xxxxx/1@xxxxxx:1521/orcl
create user xxxxxx with password 'xxxxxx';

create user xxxxxwith password 'xxxxx';


----索引
CREATE INDEX IUFO_DIS_DISCHEME1 ON NCC2111GOLD.IUFO_DIS_DISCHEME USING BTREE(TRANSRESID ASC)
CREATE INDEX IUFO_DIS_DATATYPE1 ON NCC2111GOLD.IUFO_DIS_DATATYPE USING BTREE(TRANSRESID ASC)
CREATE INDEX IUFO_DIS_DISDIR1 ON NCC2111GOLD.IUFO_DIS_DISDIR USING BTREE(TRANSRESID ASC)
CREATE INDEX IUFO_DIS_DISDIR_V1 ON NCC2111GOLD.IUFO_DIS_DISDIR_V USING BTREE(SENDERID ASC,RECEIVERID ASC,PK_ITEM ASC,TRANSRESID ASC)
CREATE INDEX UFOE_MSNAPSHOT1 ON NCC2111GOLD.UFOE_MSNAPSHOT USING BTREE(PK_MSNAPSHOT ASC)

./osrexp -usysdba/szoscar55 -p2005 -dTEST3 level=schema file=/st/backup/xxxxx.osr log=/st/backup/xxxxxx.log schema=xxxxx


./osrimp -usysdba/szoscar55 -p2005 -dtest3 file=/st/backup/xxxx.osr logfile=/st/backup/xxxx.log fromuser=NCC2111_AUTO_0311_03 touser=xxxx level=schema ignore=true

查看版本号

oscar --versiondetail
产品：神通数据库
版本：7.0.8.191204-342.71
补丁：342.71
分支：342
构建号：hb242231
操作系统：Linux
处理器架构：arm 64bit
许可连接数：200

神通命令行

1、命令行连接：isql -d osrdb -P 2003 -U sysdba/szoscar55
2、后台创建库命令（中括号代表可选）
2.1 进入后台：oscar/kstore
列出库：list database
删除库: drop database osrdb
创建库：create database osrdb [encoding 'GBK']
2.2 标准化库：oscar -o restrict -d osrdb
2.3 安装库：oscar -o install -d osrdb
2.4 卸载库：oscar -o uninstall -d osrdb
3、用户相关：
创建用户：create user test_usr with password 'test' default tablespace TS_USER_YS_USR_DATA role sysdba;
变更密码：alter user test_usr with password 'test_change';
4、权限相关：
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'));

5、查询表及字段类型备注
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/-? 帮助


echo alter database drop logfile member '/stl_db/db_data/stdb/odbs/STLCDR/STLCDR01.log'; | isql -h 132.35.96.1 -d STLCDR -Ustl_usr/stl_usr


1.--查询表空间使用情况
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;

2.--查询各节点表空间使用情况
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';


3.--查询数据文件使用情况
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;

4.--新主线查询数据文件使用情况
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;

5.--查询日志文件使用情况
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" * ("USAGERATIO")/100) / 1048576 AS "Free Size(MB)",
("REALSIZE" * "USAGERATIO"/100) / 1048576 AS "Used Size(MB)",
"USAGERATIO" AS "Used RATIO"
FROM V_SYS_LOGFILE_INFO;

6.--查询普通表空间占用情况
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;

7.--查询表分区名
select * from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_UE_17_201109';

8.--查询分区表的总大小
select sum(SIZE) from V_SYS_PARTITION_INFO_EX where RELNAME='T_YD_NORATE_17_201109';



9.--查询当前Session状态
select "SESSION ID", "APPNAME", "CURRENT SQL", "LAST SQL" from v_sys_sessions where "LOGON USER" != 'INVALID USER';

10.--查询当前Session诊断信息
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';



11.--查看等待链
select * from V$WAIT_CHAINS;
--kill掉session id
kill session 631 abort;


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

12.--列与字符串的链接
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;

13.--查询表定义
select SYS_GET_TABLEDEF from v_sys_table where tablename='XXX';

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


14.--查询计划
create table test(tc1 int) partition by hash(tc1) partitions 4;
explain select * from test;

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

16.查询依赖
查询相关表的试图
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

17.查询数据库信息
SELECT * FROM V_SYS_DATABASE_INFO;

18.查询表的级联
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);

19.查询阻塞语句sid
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;

20.查询用户默认表空间
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;



1.--创建库,表空间
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;



2.-- 创建用户

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' ;

3.-- 删除用户及所有级联

drop user JX_DW cascade;

4.-- 修改SYSDBA用户的初始密码

ALTER USER SYSDBA WITH PASSWORD 'UNIcom00';

5.-- 添加修改日志文件
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’;


6.---归档模式修改
select * from V_SYS_ARCHIVELOG_INFO;

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

ALTER DATABASE NOARCHIVELOG;(不归档)

7.--修改表空间的最大值

ALTER DATABASE "OSCAR" datafile '/kstore1/KSTORE/odbs/KSTORE/KSTORE06.dbf' AUTOEXTEND ON MAX 700G;
；
8.--序列管理

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;

9.--字符串列长度的有关查询

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

10.分裂分区
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);





审计工具 Auditor -> /settle/ShenTong/bin/auditor
安装卸载 Change Installation -> /settle/ShenTong/installation/ChangeInstallation
数据库配置工具 Database Configer -> /settle/ShenTong/bin/dbconfiger
数据迁移工具 Data Migration Tool -> /settle/ShenTong/bin/migration_java
升级工具 Datebase Update Tool -> /settle/ShenTong/bin/update
DBA管理工具 DBA Manager -> /settle/ShenTong/bin/dba
数据库维护工具 Maintenance Utility -> /settle/ShenTong/bin/oem
性能监测工具 Performance Monitor -> /settle/ShenTong/bin/perfmonitor
政策管理工具 Policy Tool -> /settle/ShenTong/bin/policy
SQL交互工具 SQL Analyzer -> /settle/ShenTong/bin/sqlanalyzer
参数配置工具 System Parameter Congfiger -> /settle/ShenTong/bin/sysviewer



物理备份：

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

备份恢复：

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"


brcmd备份和恢复使用：
-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:备份工具，提取神通内容转化为脚本文件的工具。

全库级别
osrexp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=full file=d:/backup log=d:/bk.log mode=entirety ignore=false view=true sequence=true procedure=true constraint=true trigger=true index=true
模式级别：
osrexp -hlocalhost -p2003 -usysdba/szoscar55 -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 -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=table file=d:/backup log=d:/bk.log mode=entirety ignore=false table=(messagebord.test,sysdba.test) view=false procedure=false sequence=false constraint=true trigger=true index=true

参数说明：

-h 服务器名
-p 服务器端口
-u 用户名和密码(sysdba/szoscar55)
-k 表示以操作系统用户登录
-d 数据库名称
level 备份级别
parfile 指定一个文件名,文件包含备份操作参数的列表
file 指定备份的文件
log 指定备份的日志
mode 备份方式
ignore 是否忽略不重要的错误
view 是否备份视图
sequence 是否备份序列
procedure 是否备份存储过程
constraint 是否备份表约束
trigger 是否备份表触发器
index 是否备份表索引
schema 模式级别下要备份的模式
table 表级别下要备份的表对象
single 是否只备份单个表对象
excludetable 模式级别下不要备份的表



恢复：
osrimp:恢复工具，根据osrexp工具生成的数据库备份文件还原神通内容。

全库恢复级别：
osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=full file=d:/backup log=d:/rt.log mode=entirety ignore=y recreateschema=false view=true sequence=true procedure=true recreateotherobject=true recreatetable=true constraint=true deletetabledata=true trigger=true index=true
(如果recreateschema=true 则后面的recreateotherobject=true recreatetable=true无效了)
模式恢复级别：
osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=schema file=d:/backup log=d:/rt.log mode=entirety ignore=true schema=(sysdba,public) recreateschema=false view=true sequence=true procedure=true recreateotherobject=true excludetable=(sysdba.test,public.test) recreatetable=false constraint=false deletetabledata=true trigger=false index=false
表恢复级别：
osrimp -hlocalhost -p2003 -usysdba/szoscar55 -dosrdb level=table file=d:/backup log=d:/rt.log mode=entirety ignore=true recreateschema=false view=false procedure=false sequence=true recreateotherobject=false table=(sysdba.test,public.test) recreatetable=true constraint=true deletetabledata=true trigger=true index=true
(recreatetable=true 后面的trigger=true index=true才有效)

参数说明：

-h 服务器名
-p 服务器端口
-u 用户名和密码(sysdba/szoscar55)
-k 表示以操作系统用户登录
-d 数据库名称
level 恢复级别
parfile 指定一个文件名,文件包含恢复操作参数的列表
file 指定恢复的文件
log 指定恢复的日志
mode 恢复方式
ignore 是否忽略不重要的错误
view 是否恢复视图
sequence 是否恢复序列
procedure 是否恢复存储过程
constraint 是否恢复表约束
trigger 是否恢复表触发器
index 是否恢复表索引
schema 模式级别下要恢复的模式
table 表级别下要恢复的表对象
single 是否只恢复单个表对象
excludetable 模式级别下不要恢复的表
recreateschema 是否重构模式
recreateotherobject 是否重构视图、存储过程、序列
recreatetable 是否重构表
tablespace 是否恢复表空间
deletetabledata 是否清空原表中的数据

SELECT 'KILL SESSION '||SESSION_ID||' abort;',
LTRIM(PATH,'<--') AS "阻塞链", SESSION_ID AS "我的会话ID", BLOCKER_SESSION_ID AS "我等待的会话ID", WAIT_ID,WAIT_EVENT,
WAIT_EVENT AS "等待资源(NETWAIT指等接收SQL)", NUM_WAITERS AS "多少会话在等待我",
P1_TEXT, P1, P2_TEXT, P2, P3_TEXT, P3, P4_TEXT, P4,
USER_IP AS "前端IP" , APPNAME AS "前端名", "CURRENT USER" AS "用户名", "SQL COUNT" AS "已执行SQL总数" ,
LEFT("CURRENT SQL", 81920) AS "正执行的SQL", LEFT("LAST SQL", 81920) AS "上一条执行完的SQL"
FROM
( SELECT *
FROM (SELECT LEVEL, SYS_CONNECT_BY_PATH (SESSION_ID, '<--') AS PATH, * FROM V$WAIT_CHAINS
START WITH BLOCKER_SESSION_ID IS NULL
CONNECT BY PRIOR SESSION_ID = BLOCKER_SESSION_ID)
ORDER BY LEVEL, PATH) AS VWC LEFT JOIN V_SYS_SESSIONS VSS
ON VWC.SESSION_ID=VSS."SESSION ID" ORDER BY "多少会话在等待我" DESC ;

CREATE DATABASE "NCC872" NOARCHIVELOG CONTROLFILE '/opt/ShenTong/admin/NCC872872.ctrl' LOGFILE '/data/yonyou/ShenTong/odbs/NCC872/NCC87201.log' SIZE 1000M AUTOEXTEND ON NEXT 100M TEMPFILE '/data/yonyou/ShenTong/odbs/NCC872/NCC872tmp01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M DATAFILE '/data/yonyou/ShenTong/odbs/NCC872/NCC87201.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M AUDITFILE '/data/yonyou/ShenTong/odbs/NCC872/NCC872aux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M UNDO tablespace undots01 datafile '/data/yonyou/ShenTong/odbs/NCC872/undo_tps1.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M ;