unzip ShenTong7.0.8_342.192_arm64.zip
mkdir ShenTong
cd ShenTong7.0.8_342.192_arm64
chmod 755 setup
./setup -console
source /etc/profile
service oscaragentd stop
service oscardb_OSRDBd stop
service oscaragentd start
service oscardb_OSRDBd start
ps -ef|grep os
root 6429 1 0 15:08 pts/2 00:00:00 /data/ShenTong/bin/oscaragent -e /data/ShenTong -c
root 6455 1 36 15:08 pts/2 00:00:02 /data/ShenTong/bin/oscar -o normal -d "OSRDB"
isql
Welcome to isql 2.0.56 interactive terminal, Client Default Encoding is UTF8.
Login time:2024-01-22 15:09:28
Password for default user:
szoscar55
Connect to:
神通数据库7.0.8.191204 for Linux(arm 64bit) (65535 connections) (license invalid after 90 days)
Using New Protocol. Database Encoding is UTF8.
Type: 'copyright' for distribution terms
'help' or '\h' for help with SQL commands
';' for end with commands
'!' to run system commands
'@' to run sql script
'exit' for quit
SQL=>
查看归档
select * from V_SYS_ARCHIVELOG_INFO;
查看数据文件
select * from v_sys_datafile_info;
查看集群信息
select * from v_sys_ha_slave_info;
查询用户
select username from dba_users;
修改用户密码
alter user icasc with password 'password';
创建用户
create user icasc with password 'password';
赋权
grant role rolename to user username;
开启归档
ALTER DATABASE ARCHIVELOG 'arch/OSRDB';
清理归档
CREATE OR REPLACE PROCEDURE PUBLIC.SP_DELETE_N_ARCHIVELOG("L_N" IN INT) AS
begin_time varchar(20);
end_time varchar(20);
v_sql varchar(8000);
begin
begin_time := '2019-01-01';
end_time := SYSDATE - l_n;
v_sql := 'alter database OSRDB DELETE ARCHIVELOG BETWEEN TIME '||''''||begin_time||''''||' AND '||''''||end_time||'''';
raise notice '%',v_sql;
EXECUTE IMMEDIATE v_sql;
commit;
end;
LANGUAGE 'PLOSCAR';
exec sp_delete_n_archivelog(30);
查看版本
select versiondetail;
优化
修改OSRDB.conf
物理备份命令：
brcmd -O backup -d 实例名称 -u 用户名称 -p 密码 -P 端口 -t full -k file -f 备份文件.osrbk -b 8192 -c TRUE(是否压错，true为压缩)
增量备份
brcmd -O backup -d 实例名称 -u 用户名称 -p 密码 -P 端口 -t incr-k file -f 备份文件.osrbk -b 8192 -c TRUE(是否压错，true为压缩)
用例
brcmd -O backup -d OSRDB -u SYSDBA -p szoscar55 -P 2003 -t full -k file -f /opt/OSRDB2021-06-28.osrbk -c true
物理恢复命令：
brcmd -O restore -d 实例名称 -u 用户名称 -p 密码 -P 端口 -t backup_point_last -k file -f 备份文件.osrbk -b 8192
用例
brcmd -O restore -d OSRDB -u SYSDBA -p szoscar55 -P 2003 -t backup_point_last -k file -f /opt/OSRDB2021-06-28.osrbk -b 8192
锁表查询
SELECT
LTRIM(PATH,'<--') AS "阻塞链", SESSION_ID AS "我的会话ID", BLOCKER_SESSION_ID AS "我等待的会话ID",
WAIT_EVENT AS "等待资源", NUM_WAITERS AS "多少会话在等待我",
LEFT("CURRENT SQL", 819200) AS "正执行的SQL", LEFT("LAST SQL", 819200) AS "上一条执行完的SQL",
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总数"
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";
手动建库
1）oscar打开单用户模式
2）输入命令CREATE DATABASE OSCAR ENCODING 'GBK' ARCHIVELOG '/opt/ShenTong/arch/'
3）初始化oscar -o restrict -d OSCAR
4）注册服务oscar -o install -d OSCAR
5）使环境变量生效source /etc/profile
在数据目录下面cp一份配置文件
cp OSRDB.conf OSCAR.conf
VI OSCAR.conf
PORT=2004
/etc/init.d/oscardb_OSCARd start
ALTER DATABASE DATAFILE '/data/ShenTong/odbs/OSRDB/dd.dbf' AUTOEXTEND OFF ;
ALTER TABLESPACE "dd" ADD DATAFILE '/data1/ShenTong/odbs/OSRDB/dd.dbf' SIZE 20M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ;
逻辑备份命令例子：（以模式名为TEST为例）
osrexp -usysdba/szoscar55 -h127.0.0.1 -p2003 -dosrdb level=schema file=/opt/TEST.osr log=/opt/bk.log schema=TEST
逻辑恢复命令例子：（以模式名为TEST为例）
osrimp -usysdba/szoscar55 -h127.0.0.1 -p2003 -dosrdb level=schema file=/opt/TEST.osr log=/opt/rt.log schema=TEST ignore=true checkvalidate=false
集群配置
alter database set node list node 1 '192.168.7.1' ,node 2 '192.168.7.2';
修改每个节点配置文件
HOTSTANDBY_DATABASE_TYPE=2
ENABLE_HA_SINGLE_ALIVE=false
HA_LOCAL_NET_DEV_NAME='ens33:1' ### 这个网卡设备名就是服务IP对应的设备，具体解释见下方注解
HA_SERVER_IP_ADDRESS='IP' ### vip，这个是对外提供服务的浮动IP
HA_GATEWAY='gate## 实际网关IP地址
HA_SUB_MASK='255.255.255.0' ### 子网掩码
HA_SLAVE_QUERY_WAIT_TIMEOUT=5000
HA_ELECTION_TIMEOUT_MS=10000
重启数据库
--1. 查看系统内存总体分布统计信息，我们需要根据总体分布缩小分析问题的范围。
select V$MEMORY.MEMORY_NAME,MEMORY_SIZE/1024 as "MEMORY_SIZE(k)",MEMORY_COUNT,ALLOC_COUNT,FREE_COUNT,MEMORY_DESC from V$MEMORY，V$MEMORY_DESC where V$MEMORY.MEMORY_NAME=V$MEMORY_DESC.MEMORY_NAME order by MEMORY_SIZE desc;
--2. 查看全局heap内存占用情况，按照堆名汇总 V$MEMORY_GLOBAL_HEAP ，将同名的堆的大小统计到一起
select HEAP_NAME,TOTAL_SIZE/1024 as "TOTAL_SIZE(k)",FREE_SIZE/1024 as "FREE_SIZE(k)",INIT_SIZE/1024 as "INIT_SIZE(k)",BLOCK_ALLOC_SIZE/1024 as "BLOCK_ALLOC_SIZE(k)",BIG_BLOCK_ALLOC_SIZE/1024 as "BIG_BLOCK_ALLOC_SIZE(k)",HEAP_COUNT from V$MEMORY_GLOBAL_HEAP_SUMMARY order by TOTAL_SIZE desc ;
--3. 查看私有heap内存统计情况，按照堆名汇总 V$MEMORY_PRIVATE_HEAP ，将同名的堆的大小统计到一起
select HEAP_NAME,TOTAL_SIZE/1024 as "TOTAL_SIZE(k)",FREE_SIZE/1024 as "FREE_SIZE(k)",INIT_SIZE/1024 as "INIT_SIZE(k)", BLOCK_ALLOC_SIZE/1024 as "BLOCK_ALLOC_SIZE(k)",BIG_BLOCK_ALLOC_SIZE/1024 as "BIG_BLOCK_ALLOC_SIZE(k)",HEAP_COUNT,SESSION_COUNT,MAX_SESSION_SIZE/1024 as "MAX_SESSION_SIZE(k)",MAX_SESSION_ID,MAX_SESSION_BACKEND from V$MEMORY_PRIVATE_HEAP_SUMMARY order by TOTAL_SIZE desc;
--4. 查看私有chunk内存统计情况，按照内存分配的名字汇总 v$memory_private_chunk ， 将同名的统计值统计到一起。并统计出占用内存最大chunk的session_id 以及该session 是否是后台进程
select STAT_NAME,MEMORY_SIZE/1024 as "MEMORY_SIZE(k)",SESSION_COUNT, MAX_SESSION_SIZE/1024 as "MAX_SESSION_SIZE(k)",MAX_SESSION_ID,MAX_SESSION_BACKEND from V$MEMORY_PRIVATE_CHUNK_SUMMARY order by MEMORY_SIZE desc;
--5. 查看全局chunk内存占用情况
select STAT_NAME,MEMORY_SIZE/1024 as "MEMORY_SIZE(k)" from V$MEMORY_GLOBAL_CHUNK order by MEMORY_SIZE desc;
--6. 查看全局heap内存占用情况
select * from V$MEMORY_GLOBAL_HEAP order by TOTAL_SIZE desc;
--7. 查看私有CHUNK内存占用情况
select * from V$MEMORY_PRIVATE_CHUNK order by MEMORY_SIZE desc;
--8. 查看私有heap内存占用情况
select * from V$MEMORY_PRIVATE_HEAP order by TOTAL_SIZE desc;
--9. 查看系统全局内存各个区内存的分布情况统计
select * from V$GLOBAL_MEMORY_STAT order by MEMORY_SIZE desc;
--10. 查看当前线程私有内存分布情况统计
select * from V$PRIVATE_MEMORY_STAT order by MEMORY_SIZE desc;
扫一扫
4146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
