Linux查看hana数据库进程,HANA数据库备份查看和还原

1.检查trace文件cat backup.log |grep "/usr/sap/HEQ/HDB00/backup/data/"

wKiom1g1MlaCEXCsAAeQU0rfze0504.jpg (465.23 KB, 下载次数: 20)

2016-11-25 09:08 上传

2.通过studio检查catalog

wKioL1g1Mq_R3TJ1AAHdIq8wz2Y102.jpg (117.88 KB, 下载次数: 26)

2016-11-25 09:08 上传

3.查看视图M_BACKUP_CATALOG   ---complete data backup/incremental data backup/differential data backup

1

2

3

4select * from "M_BACKUP_CATALOG" where

ENTRY_TYPE_NAME =

'complete data backup' and state_name='successful'

order by sys_start_time desc;

1

2

3

4

5

6

7

8ENTRY_ID,ENTRY_TYPE_NAME,BACKUP_ID,SYS_START_TIME,UTC_START_TIME,SYS_END_TIME,UTC_END_TIME,STATE_NAME,COMMENT,MESSAGE,SYSTEM_ID

1479879019957,"complete data backup",1479879019957,"2016-11-23 13:30:19.957000000","2016-11-23 05:30:19.957000000","2016-11-23 13:47:01.453000000","2016-11-23 05:47:01.453000000","successful","","",""

1479874724234,"complete data backup",1479874724234,"2016-11-23 12:18:44.234000000","2016-11-23 04:18:44.234000000","2016-11-23 12:33:37.222000000","2016-11-23 04:33:37.222000000","successful","","",""

1479872660066,"complete data backup",1479872660066,"2016-11-23 11:44:20.066000000","2016-11-23 03:44:20.066000000","2016-11-23 12:01:43.350000000","2016-11-23 04:01:43.350000000","successful","","",""

1479870313939,"complete data backup",1479870313939,"2016-11-23 11:05:13.939000000","2016-11-23 03:05:13.939000000","2016-11-23 11:22:28.214000000","2016-11-23 03:22:28.214000000","successful","","",""

1479869176913,"complete data backup",1479869176913,"2016-11-23 10:46:16.913000000","2016-11-23 02:46:16.913000000","2016-11-23 11:03:19.029000000","2016-11-23 03:03:19.029000000","successful","","",""

1479800886690,"complete data backup",1479800886690,"2016-11-22 15:48:06.690000000","2016-11-22 07:48:06.690000000","2016-11-22 16:04:25.923000000","2016-11-22 08:04:25.923000000","successful","","",""

1479110498231,"complete data backup",1479110498231,"2016-11-14 16:01:38.231000000","2016-11-14 08:01:38.231000000","2016-11-14 16:16:56.940000000","2016-11-14 08:16:56.940000000","successful","","",""

4.如果想检查某一个备份是否OK可以这么做

hdbbackupcheck backup_filename

1

2

3heqadm@ECQ:/usr/sap/HEQ/HDB00/backup/data/dbsqlbackup> hdbbackupcheck 2016-11-23.complete_databackup_0_1

Backup '/hana/shared/HEQ/HDB00/backup/data/dbsqlbackup/2016-11-23.complete_databackup_0_1' successfully checked.

heqadm@ECQ:/usr/sap/HEQ/HDB00/backup/data/dbsqlbackup>

hdbbackupcheck -v backup_filename   加v查看详细信息

还原数据库可以通过studio，下面是命令行还原过程：

1.To call recoverSys.py, enter the statement in the following format: HDBSettings.sh recoverSys.py

[]

If you run HDBSettings.sh recoverSys.py without any parameters, recoverSys.py performs a

recovery to the most recent point in time

在HDB运行的时候删掉一个数据文件，做一个默认还原恢复

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24heqadm@ECQ:/usr/sap/HEQ/SYS/global/hdb/data/mnt00001/hdb00003> rm -rf datavolume_0000.dat

heqadm@ECQ:/usr/sap/HEQ/HDB00/eccqas/trace> HDBSettings.sh recoverSys.py

[140219726718720, 0.002] >> starting recoverSys (at Thu Nov 24 09:20:08 2016)

[140219726718720, 0.002] args: ()

[140219726718720, 0.002] keys: {}

own pid: 27909

recoverSys started: 2016-11-24 09:20:08

testing master: eccqas

eccqas is master

shutdown database, timeout is 120

stop system

stop system: eccqas

stopping system: 2016-11-24 09:20:08

stopped system: 2016-11-24 09:20:59

creating file recoverInstance.sql

restart database

restart master nameserver: 2016-11-24 09:21:04

start system: eccqas

2016-11-24T09:21:14+08:00  P028061      15893ec1ad5 INFO    RECOVERY state of service: nameserver, eccqas:30001, volume: 1, RecoveryExecuteTopologyAndSSFSRecoveryFinished

recoverSys finished successfully: 2016-11-24 09:21:15

[140219726718720, 66.334] 0

[140219726718720, 66.334] << ending recoverSys, rc = 0 (RC_TEST_OK), after 66.332 secs

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15可以此时可以查看iostat-d 1 等待IO完成HDB自动开启发现，数据库已经恢复到最近正常状态

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             166.00    151552.00     16392.00     151552      16392

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             215.00    187392.00     32768.00     187392      32768

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             224.00    201216.00     27648.00     201216      27648

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             233.00    217088.00     21504.00     217088      21504

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             213.86    176918.81     40558.42     178688      40964

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             246.00    216064.00     35840.00     216064      35840

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn

sda             226.00    193536.00     37888.00     193536      37888

1

2

3

4

5

6

7

8

9

10

11

12

13heqadm@ECQ:/usr/sap/HEQ/HDB00/eccqas/trace> sapcontrol -nr 00 -function GetProcessList

24.11.2016 09:41:56

GetProcessList

OK

name, description, dispstatus, textstatus, starttime, elapsedtime, pid

hdbdaemon, HDB Daemon, GREEN, Running, 2016 11 24 09:21:05, 0:20:51, 28043

hdbcompileserver, HDB Compileserver, GREEN, Running, 2016 11 24 09:21:16, 0:20:40, 28131

hdbindexserver, HDB Indexserver, GREEN, Running, 2016 11 24 09:21:22, 0:20:34, 28200

hdbnameserver, HDB Nameserver, GREEN, Running, 2016 11 24 09:21:06, 0:20:50, 28061

hdbpreprocessor, HDB Preprocessor, GREEN, Running, 2016 11 24 09:21:16, 0:20:40, 28134

hdbwebdispatcher, HDB Web Dispatcher, GREEN, Running, 2016 11 24 09:41:20, 0:00:36, 28635

hdbxsengine, HDB XSEngine, GREEN, Running, 2016 11 24 09:21:22, 0:20:34, 28202

heqadm@ECQ:/usr/sap/HEQ/HDB00/eccqas/trace>

12.指定文件，之前备份的命令是："backup data using file ('$date.complete')"

那么还原的命令是：RECOVER DATA USING file ('2016-11-23.complete')1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24heqadm@ECQ:/usr/sap/HEQ/SYS/global/hdb/data/mnt00001/hdb00003> HDBSettings.sh recoverSys.py --command="RECOVER DATA USING file ('2016-11-23.complete') CLEAR LOG"

[139945440958208, 0.002] >> starting recoverSys (at Thu Nov 24 11:28:56 2016)

[139945440958208, 0.002] args: ()

[139945440958208, 0.002] keys: {'command': "RECOVER DATA USING file ('2016-11-23.complete') CLEAR LOG"}

own pid: 30597

recoverSys started: 2016-11-24 11:28:56

testing master: eccqas

eccqas is master

shutdown database, timeout is 120

stop system

stop system: eccqas

stopping system: 2016-11-24 11:28:56

stopped system: 2016-11-24 11:28:56

creating file recoverInstance.sql

restart database

restart master nameserver: 2016-11-24 11:29:01

start system: eccqas

2016-11-24T11:29:11+08:00  P030717      15894613fe1 INFO    RECOVERY state of service: nameserver, eccqas:30001, volume: 1, RecoveryExecuteTopologyAndSSFSRecoveryFinished

recoverSys finished successfully: 2016-11-24 11:29:11

[139945440958208, 15.490] 0

[139945440958208, 15.491] << ending recoverSys, rc = 0 (RC_TEST_OK), after 15.489 secs

heqadm@ECQ:/usr/sap/HEQ/SYS/global/hdb/data/mnt00001/hdb00003>

The CLEAR LOG option prevents the recovery of entries from the log area. No log entries from the log area are replayed, and the log area is initialized. As a consequence, the content of the log area is lost.

If they are needed, the log entries from the log backups are replayed.

CLEAR LOG must be used in the following situations:The log area is unusable.

You are recovering the database to a different system.The CLEAR LOG command corresponds to the Initialize log area option in SAP HANA studio.

3.查看可以还原恢复到哪个时间点

1821207 - Determining required recovery files

1705945 - Determining required restoration files1

2

3

4

5

6

7

8

9

10

11

12

13

14

15heqadm@ECQ:/hana/shared/HEQ/HDB00/backup/data/dbsqlbackup> hdbbackupdiag -f -d /hana/shared/HEQ/HDB00/backup/log/

found backup catalog 1479887651122 from file /hana/shared/HEQ/HDB00/backup/log/log_backup_0_0_0_0.1479887651122

using backup catalog 1479887651122 from file /hana/shared/HEQ/HDB00/backup/log/log_backup_0_0_0_0.1479887651122

2016-11-23.complete_databackup_0_1

2016-11-23.increment_databackup_incremental_0_1479887451566_0_1

2016-11-23.complete_databackup_1_1

2016-11-23.increment_databackup_incremental_1479879019957_1479887451566_1_1

2016-11-23.complete_databackup_2_1

2016-11-23.increment_databackup_incremental_1479879019957_1479887451566_2_1

2016-11-23.complete_databackup_3_1

2016-11-23.increment_databackup_incremental_1479879019957_1479887451566_3_1

log_backup_1_0_1677888_1679488.1479887648977

log_backup_2_0_11973184_11976576.1479887565796

log_backup_3_0_4965350720_4965451904.1479887545567

heqadm@ECQ:/hana/shared/HEQ/HDB00/backup/data/dbsqlbackup>

1

2

3

4

5

6

7

8

9

10

11

12

13heqadm@ECQ:/hana/shared/HEQ/HDB00/backup/data/dbsqlbackup> hdbbackupdiag -d /hana/shared/HEQ/HDB00/backup/data/dbsqlbackup -b 2016-11-23.complete_databackup

Backup file: /hana/shared/HEQ/HDB00/backup/data/dbsqlbackup/2016-11-23.complete_databackup_0_1

Backup time: 2016-11-23T13:30:21+08:00

Backup file: /hana/shared/HEQ/HDB00/backup/data/dbsqlbackup/2016-11-23.complete_databackup_2_1

Backup time: 2016-11-23T13:30:21+08:00

RedoLogPosition: 11925379

Backup file: /hana/shared/HEQ/HDB00/backup/data/dbsqlbackup/2016-11-23.complete_databackup_1_1

Backup time: 2016-11-23T13:30:21+08:00

RedoLogPosition: 1652418

Backup file: /hana/shared/HEQ/HDB00/backup/data/dbsqlbackup/2016-11-23.complete_databackup_3_1

Backup time: 2016-11-23T13:30:21+08:00

RedoLogPosition: 4942939522

heqadm@ECQ:/hana/shared/HEQ/HDB00/backup/data/dbsqlbackup>

1-rw-r----- 1 heqadm sapsys 6483968 Nov 23 15:54 log_backup_3_0_4965350720_4965451904.1479887545567

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22heqadm@ECQ:/usr/sap/HEQ/HDB00/backup/log> HDBSettings.sh recoverSys.py --command="RECOVER DATABASE UNTIL TIMESTAMP '2016-11-23 15:54:00' CLEAR LOG"

[140447315224320, 0.002] >> starting recoverSys (at Thu Nov 24 14:11:26 2016)

[140447315224320, 0.002] args: ()

[140447315224320, 0.002] keys: {'command': "RECOVER DATABASE UNTIL TIMESTAMP '2016-11-23 15:54:00' CLEAR LOG"}

own pid: 6290

recoverSys started: 2016-11-24 14:11:26

testing master: eccqas

eccqas is master

shutdown database, timeout is 120

stop system

stop system: eccqas

stopping system: 2016-11-24 14:11:26

stopped system: 2016-11-24 14:11:26

creating file recoverInstance.sql

restart database

restart master nameserver: 2016-11-24 14:11:31

start system: eccqas

2016-11-24T14:11:41+08:00  P006410      15894f606ef INFO    RECOVERY state of service: nameserver, eccqas:30001, volume: 1, RecoveryExecuteTopologyAndSSFSRecoveryFinished

recoverSys finished successfully: 2016-11-24 14:11:42

[140447315224320, 15.485] 0

[140447315224320, 15.485] << ending recoverSys, rc = 0 (RC_TEST_OK), after 15.483 secs

heqadm@ECQ:/usr/sap/HEQ/HDB00/backup/log>

可以指定备份和日志的目录

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22heqadm@ECQ:/usr/sap/HEQ/HDB00> HDBSettings.sh recoverSys.py --command="RECOVER DATABASE UNTIL TIMESTAMP '2016-11-23 15:54:00' CLEAR LOG USING DATA PATH ('/usr/sap/HEQ/HDB00/backup/data/dbsqlbackup/backup/') USING LOG PATH ('/usr/sap/HEQ/HDB00/backup/log/')"

[140659884533504, 0.002] >> starting recoverSys (at Thu Nov 24 16:22:37 2016)

[140659884533504, 0.002] args: ()

[140659884533504, 0.002] keys: {'command': "RECOVER DATABASE UNTIL TIMESTAMP '2016-11-23 15:54:00' CLEAR LOG USING DATA PATH ('/usr/sap/HEQ/HDB00/backup/data/dbsqlbackup/backup/') USING LOG PATH ('/usr/sap/HEQ/HDB00/backup/log/')"}

own pid: 15906

recoverSys started: 2016-11-24 16:22:37

testing master: eccqas

eccqas is master

shutdown database, timeout is 120

stop system

stop system: eccqas

stopping system: 2016-11-24 16:22:37

stopped system: 2016-11-24 16:22:37

creating file recoverInstance.sql

restart database

restart master nameserver: 2016-11-24 16:22:42

start system: eccqas

2016-11-24T16:22:52+08:00  P016026      158956e207b INFO    RECOVERY state of service: nameserver, eccqas:30001, volume: 1, RecoveryExecuteTopologyAndSSFSRecoveryFinished

recoverSys finished successfully: 2016-11-24 16:22:53

[140659884533504, 16.000] 0

[140659884533504, 16.000] << ending recoverSys, rc = 0 (RC_TEST_OK), after 15.998 secs

heqadm@ECQ:/usr/sap/HEQ/HDB00>