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表示最大可能压缩,压缩时间最长


posted @   踏雪无痕2017  阅读(1181)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示