SAP HANA 基本操作
HANA 基本操作
su - hdbadm
--创建租户数据库
hdbsql -u SYSTEM -p 'P@ssw0rd' -i 00 -d SYSTEMDB 'CREATE DATABASE TEST SYSTEM USER PASSWORD "P@ssw0rd"'
--备份系统库以及租户库
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA USING FILE ('/backup/initialbackupSYS')"
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP LOG USING FILE ('/backup/initialbackupSYS_LOG')"
hdbsql -d TEST -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA USING FILE ('/backup/initialbackupTEST')"
hdbsql -d TEST -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP LOG USING FILE ('/backup/initialbackupTEST_LOG')"
hdbsql -d TEST -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA USING FILE ('/backup/initialbackupTEST') ASYNCHRONOUS" --后台运行备份任务
hdbsql -d TEST -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA USING FILE ('/backup/initialbackupTEST') INCLUDE CONFIGURATION" --备份包括用户配置
hdbsql -d TEST -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP LOG USING FILE ('/backup/initialbackupTEST_LOG') ASYNCHRONOUS" --后台运行备份任务
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA FOR FULL SYSTEM USING FILE ('/backup/initialbackup')" --备份系统库以及所有的租户库
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP DATA FOR FULL SYSTEM CREATE SNAPSHOT COMMENT 'SNAPSHOT-2023-06-05'" --通过创建快照备份系统库以及所有的租户库
--恢复系统库以及租户库
--恢复系统库
HDBSettings.sh recoverSys.py --wait //恢复系统库至最近的时间点,并且等待恢复完成
HDBSettings.sh recoverSys.py --command="RECOVER DATABASE UNTIL TIMESTAMP '2023-10-22 15:00:00'" --wait //恢复系统库至指定的时间点,并且等待恢复完成
HDBSettings.sh recoverSys.py --command="RECOVER DATABASE UNTIL TIMESTAMP '2018-10-22 15:00:00' USING CATALOG PATH ('/remote/backup/CHH/catalog') USING BACKUP_ID 1380740407446 CHECK ACCESS USING FILE" --wait //恢复系统库至指定的时间点,指定backup catalog 位置,检查备份文件的有效性,并且等待恢复完成
--恢复租户库
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "RECOVER DATA USING FILE ('/backup/THURSDAY') CLEAR LOG" --恢复数据备份,初始化日志区
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "RECOVER DATA FOR Tenant_1 USING SOURCE 'Tenant_0@PR1' USING BACKINT('2017-09-21') CLEAR LOG" --将Tenant_0 基于backint 的数据备份恢复到目标库Tenant_1
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "RECOVER DATA FOR Tenant_1 USING SOURCE 'Tenant_0@PR1' USING DATA PATH ('/hana/PR1/backup/data') USING CATALOG PATH ('/hana/PR1/backup/catalog') CLEAR LOG" --将Tenant_0 基于文件的数据备份恢复到目标库Tenant_1
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "RECOVER DATABASE FOR Tenant_1 UNTIL TIMESTAMP '2017-09-21 15:00:00' USING DATA PATH ('/backup/THURSDAY/') USING LOG PATH ('/backup/logs1/','/backup/logs2/') CHECK ACCESS " --将Tenant_1恢复到特定的时间点
--清理备份文件以及catalog
hdbsql -d SYSTEMDB -u SYSTEM -p 'P@ssw0rd' -i 00 "BACKUP CATALOG DELETE FOR TESTDB ALL BEFORE BACKUP_ID 1496915612668 COMPLETE " --清理早于指定的backup_id的备份文件以及catalog
--启动、停止、重新启动HANA服务
su - hdbadm
--sapcontrol
hdbadm>sapcontrol -nr 00 -function StopSystem
hdbadm>sapcontrol -nr 00 -function StartSystem
hdbadm>sapcontrol -nr 00 -function RestartSystem
hdbadm>sapcontrol -nr 00 -function GetSystemInstanceList
hdbadm>sapcontrol -nr 00 -function GetProcessList
--HDB
HDB STOP
HDB START
HDB RESTART
--启动、停止租户库服务
hdbsql=>alter system start database TEST
hdbsql=>alter system stop database TEST2
--调整日志模式
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence', 'log_mode') = 'overwrite' WITH RECONFIGURE
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('persistence', 'log_mode') = 'normal' WITH RECONFIGURE
--清理日志文件,多租户模式需要在每个租户库执行
ALTER SYSTEM RECLAIM LOG
--导出、导入数据(数据格式可以是csv或者二进制,行存储表数据格式只能是csv)
1.导出指定对象的结构(导出tab1的结构)
EXPORT tab1 AS CSV INTO '/tmp/hana_export' WITH REPLACE CATALOG ONLY
2.导出指定对象的结构和数据 (导出tab1的结构和数据)
EXPORT tab1 AS CSV INTO '/tmp/hana_export’ WITH REPLACE STRIP THREADS 2
3.导出schema 下面的对象的结构和数据(导出schema sapabap1 的结构和数据)
EXPORT sapabap1.* AS CSV INTO ‘/tmp/hana_export’ WITH REPLACE STRIP THREADS 2
4.导入指定对象的结构(导入tab1的结构)
IMPORT tab1 AS CSV FROM ‘/tmp/hana_import’ WITH REPLACE CATALOG ONLY THREADS 2
5.导入指定对象的结构和数据(导入tab1的结构和数据)
IMPORT tab1 AS CSV FROM ‘/tmp/hana_import’ WITH REPLACE STRIP THREADS 2
6.导入schema中所有对象的结构和数据(导入schema sapabap1的结构和数据)
IMPORT sapabap1.* AS CSV FROM ‘/tmp/hana_import’ WITH REPLACE STRIP THREADS 2
7.导入数据文件示例
IMPORT FROM CSV FILE ‘/data/data.csv’ INTO tab1 WITH
THREADS 10
BATCH 10000
LOCK TABLE -–提高列表初始装载性能
RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘||’
DATA FORMAT ‘YYYY-MM-DD’
ERROR LOG ‘/data/mytab.log’
--控制文件示例
IMPORT DATA
INTO TABLE tab1
FROM ‘/data/data.csv’
RECORD DELIMITED BY ‘\n’
FIELD DELIMITED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
ERROR LOG ‘/data/import.log’
–使用控制文件导入数据
IMPORT FROM CONTROL FILE ‘/data/data.ctl’
--限制内存以及cpu使用参数(内存单位为M,CPU单位为核心数)
global.ini->memorymanager->global_allocation_limit
global.ini->execution->max_concurrency
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'SYSTEM') SET ('memorymanager', 'global_allocation_limit') = '8192' WITH RECONFIGURE;
--限制sql 语句内存使用
global.ini >resource_tracking> enable_tracking = on
global.ini >resource_tracking> memory_tracking = on
alter system alter configuration ('global.ini','SYSTEM') SET ('memorymanager', 'statement_memory_limit') = 100 with reconfigure;
--查询内存限制大小
select HOST, round(ALLOCATION_LIMIT/1024/1024/1024, 2) as "Allocation Limit GB" from PUBLIC.M_HOST_RESOURCE_UTILIZATION
--启用备份压缩(hana 2 sps06 以及以上版本支持)
global.ini->backup->backup-data_backup_compression_algorithm=lz4 --压缩算法
global.ini->backup->data_backup_compression_level=0 --压缩基本,0至65537,0表示最大可能压缩,压缩时间最长
本文来自博客园,作者:踏雪无痕2017,转载请注明原文链接:https://www.cnblogs.com/oradba/p/17384164.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步