思极有容数据库产品常用管理脚本

用户管理

--修改用户密码、授权

alter user SYSDBA IDENTIFIED BY "SG-RDB123"; --修改系统默认用户的密码

create user TEST IDENTIFIED BY "SG-RDB123"; --增加管理员用户

GRANT resource TO DMDBA; --分配resource角色给TEST用户

--创建表空间、增加表空间数据文件

createtablespace"TEST"datafile'/opt/dm8/data/SG-RDB/TEST.DBF'size32autoextendonnext100maxsize20480CACHE= NORMAL;

altertablespace"TEST"adddatafile'/opt/dm8/data/SG-RDB/TEST2.DBF'size32autoextendonnext100maxsize20480;

--创建用户指定表空间

createusertestIDENTIFIEDBY"SG-RDB123"defaulttablespaceTEST;

GRANTresourcetotest;

• 归档及备份

--开启归档

alter database mount;

alter database archivelog;

--添加归档指定路径及大小

alter database add archivelog 'dest=/opt/dm8/arch,type=local,file_size=1024,space_limit=102400';

alter database open;

--全量备份

backupdatabasefulltodmbak_full_01 bakfile '/opt/dm8/bak/dmbak_full_01.bak'compressed;

--基于备份集增量备份

backupdatabaseincrementwithbackupdir '/opt/dm8/bak'todmbak_increment_00 bakfile '/opt/dm8/bak/dmbak_increment_00.bak'compressed;

--使用作业备份

---开启代理作业

SP_INIT_JOB_SYS(1);

--定时每周六运行，进行全量备份

callSP_CREATE_JOB ('JOB_FULL_BAK_TIMELY',1,0,'',0,0,'',0,'定时全量备份');

callSP_JOB_CONFIG_START('JOB_FULL_BAK_TIMELY');

callSP_ADD_JOB_STEP('JOB_FULE_BAK_TIMELY','STEP_FULL_BAK',5,'01000/dm7/data/bak',0,0,0,0,'/dm7/data/job.log',1); 

callSP_ADD_JOB_SCHEDULE('JOB_FULE_BAK_TIMELY','SCHEDULE_FULL_BAK',1,2,1,64,0,'00:05:56',NULL,'2020-05-20 22:22:22',NULL,'');

callSP_JOB_CONFIG_COMMIT('JOB_FULL_BAK_TIMELY');

--定时每天运行，进行增量备份(晚上)

callSP_CREATE_JOB('JOB_INCREMENT_BAK_TIMELY_NIGHT',1,0,'',0,0,'',0,'定时增量备份');

callSP_JOB_CONFIG_START('JOB_INCREMENT_BAK_TIMELY_NIGHT');

callSP_ADD_JOB_STEP('JOB_INCREMENT_BAK_TIMELY_NIGHT','STEP_INCREMENT_BAK',5,'11000/dm7/data/bak|/dm7/data/dm7',0,0,0,0,'/dm7/data/job.log',1);

callSP_ADD_JOB_SCHEDULE('JOB_INCREMENT_BAK_TIMELY_NIGHT','SCHEDULE_INCREMENT_BAK',1,1,1,0,0,'02:00:00',NULL,'2020-05-20 22:22:22',NULL,'');

callSP_JOB_CONFIG_COMMIT('JOB_INCREMENT_BAK_TIMELY_NIGHT');

--定时每周日运行，删除前30天的备份，包括全量和增量

callSP_CREATE_JOB('JOB_DEL_BAK_TIMELY',1,0,'',0,0