Dameng uses online SQL to perform tablespace restore (1)

Performing table space restore using disql

Describes how to restore tablespaces using the disql tool. The main contents include:

1. Restore Tablespaces Using Tablespace Full Backup

2. Restore Tablespaces Using Tablespace Incremental Backup

3. Restore tablespaces using database backups

1. Restore Tablespaces Using Tablespace Full Backup

The restore and restore operations of the table space are completed at one time, so there is no need to perform the restore operation after the restore. The complete procedure for restoring a table space using a table space full backup is as follows:

1)Configuration Archive.

2)Ensure that the database is in OPEN or MOUNT state.

3)Backup tablespaces.

SQL> backup tablespace main to ts_main_bak backup 'E:\dmdbms\backup\ts_main_bak'; Operation Executed Elapsed Time: 00:00:01.069. Execution number:9.

4)Verify table space backup. This step is optional. If it is determined that the backup file is legal, backup verification may not be performed.

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_bak'); row number SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_bak')---------- ------------------------------------------------------1 1 Elapsed time: 17.741(ms). Execution number:10.

5)Modify tablespaces to offline.

SQL> alter tablespace main offline; operation executed Elapsed time: 137.581(milliseconds). Implementation No.:11.

6)Restore tablespaces.

SQL> restore tablespace main from backend 'E:\dmdbms\backup\ts_main_bak'; operation elapsed time: 276.897(milliseconds). Execution No.:12.

7)Modify tablespaces to online.

SQL> alter tablespace main online; operation executed Elapsed time: 17.971(milliseconds). Execution No.:13.

2. Restore Tablespaces Using Tablespace Incremental Backup

in that case where an incremental backup is in the same directory as a base backup, the procedure for restore a tablespace using an incremental backup are the same as for a full backup. RESTORE TABLESPACE is required when incremental backups are not in the same directory as base backups... WITH BACKUPDIR statement reverts. Here is an example of how to restore a table space using incremental backups. The procedure is as follows:

1)Configuration Archive.

2)Ensure that the database is in OPEN or MOUNT state.

3)Fully backup tablespaces.

SQL> backup tablespace main to ts_main_full_bak backup 'E:\dmdbms\backup\ts_main_full_bak'; operation executed Elapsed time: 00:00:01.095. Execution number:35.

4)Multiple incremental backups of tablespaces.

SQL> begin2 for i in 1 .. 100 loop3 insert into t1 values(i);4 end loop;5 commit;6 end;7 /DMSQL procedure completed successfully Elapsed time: 3.383 milliseconds. Execution Number:36.SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak' backupset 'E: \dmdbms\backup1\ts_main_incr_bak_01'; Operation Executed Elapsed Time: 00:00:01.079. Execution number:37.SQL> begin2 for i in 101 .. 200 loop3 insert into t1 values(i);4 end loop;5 end;6 /DMSQL procedure completed successfully Elapsed time: 1.274(milliseconds). Execution Number:38.SQL> alter system switch logfile; Operation elapsed time: 14.166(milliseconds). Execution number:0.SQL> backup tablespace main increment with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E:\dmdbms\backup1\ts_main_incr_bak_01' backupset 'E: \dmdbms\backup2\ts_main_incr_bak_02'; Operation executed elapsed time: 00:00:01.081. Execution number:39.

5)Verify table space backup. This step is optional. If it is determined that the backup file is legal, backup verification may not be performed.

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_main_full_bak'); row number SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_main_full_bak')---------- -----------------------------------------------------------1 1 Elapsed time: 20.931(ms). Execution Number:40.SQL> select sf_bakset_check ('disk ','E: \dmdbms\backup1\ts_main_incr_bak_01'); Line Number SF_BAKSET_CHECK('disk','E:\dmdbms\backup1\ts_main_incr_bak_01')---------- ---------------------------------------------------------------1 1 Elapsed time: 31.184(ms). Execution Number:41.SQL> select sf_bakset_check ('disk ','E: \dmdbms\backup2\ts_main_incr_bak_02'); Line Number SF_BAKSET_CHECK('disk','E:\dmdbms\backup2\ts_main_incr_bak_02')---------- ---------------------------------------------------------------1 1 Elapsed time: 25.171(ms). Execution number:42.

6)Modify Tablespace to Offline

SQL> alter tablespace main offline; operation executed Elapsed time: 98.420(milliseconds). Execution number:43.

7)Restore tablespaces using incremental backup E:\dmdbms\backup2\ts_main_incr_bak_02.

SQL> restore tablespace main from backend 'E:\dmdbms\backup2\ts_main_incr_bak_02' with backupdir 'E:\dmdbms\backup\ts_main_full_bak','E: \dmdbms\backup1\ts_main_incr_bak_01'; Elapsed time: 370.046(milliseconds). Execution number:44.

8)Modify tablespaces to online.

SQL> alter tablespace main online; operation executed Elapsed time: 15.717(milliseconds). Execution number:45.SQL> select count(*) from t1; line number COUNT(*)---------- --------------------1 200 elapsed time: 1.345(ms). Execution number:46.

3. Restore tablespaces using database backups

Restoring tablespaces using database backups allows you to quickly restore one or more tablespaces without affecting other tablespaces and objects in the database. You can choose to restore in this way when only certain tablespaces in the database are corrupted and there are no tablespaces backed up.

The specific steps are as follows:

1)Configuration Archive.

2)Ensure that the database is in OPEN or MOUNT state.

3)Create a full database backup.

SQL> backup database full to db_jydm_full_bak backup 'E:\dmdbms\backup\ts_jydm_full_bak'; operation executed Elapsed time: 00:00:01.198. Execution number:50.

4)Verify table space backup. This step is optional. If it is determined that the backup file is legal, backup verification may not be performed.

SQL> select sf_bakset_check('disk','E:\dmdbms\backup\ts_jydm_full_bak'); row number SF_BAKSET_CHECK('disk','E:\dmdbms\backup\ts_jydm_full_bak')---------- -----------------------------------------------------------1 1 Elapsed time: 46.115(ms). Execution number:51.

5)Modify tablespaces to offline.

SQL> alter tablespace main offline; operation executed Elapsed time: 96.596(milliseconds). Execution number:52.

6)Restore tablespaces.

SQL> restore tablespace main from backend 'E:\dmdbms\backup\ts_jydm_full_bak'; operation elapsed time: 326.842(milliseconds). Execution number:53.

7)Modify tablespaces to online.

SQL> alter tablespace main online; operation executed Elapsed time: 17.338(milliseconds). Execution number:54.SQL> select count(*) from t1; line number COUNT(*)---------- --------------------1 200 elapsed time: 1.969(ms). Execution number:55.