Manually clear the backup path or archive path

After you replace the backup path or archive path, you can clear the original backup or archive path that is no longer used. Clearing the data backup path or archive path means clearing all backup sets or log archive pieces in the path.

Limitations

• You can only clear the backup or archive path that is being replaced. You cannot clear the backup or archive path that is currently in use.

• You cannot set the cleared old path back to the backup or archive path.

Clear the backup path

Clear the backup path of a user tenant as the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

   The following example shows how to connect to the database. Please replace the actual environment variables.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Query the tenant information to obtain the TENANT_ID.

   The following example shows how to query the mysql001 tenant:

   obclient(root@sys)[(none)]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql001';
   

   The query result is as follows:

   +-----------+-------------+-------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE |
   +-----------+-------------+-------------+
   |      1002 | mysql001    | USER        |
   +-----------+-------------+-------------+
   1 row in set
   

   From the query result, the TENANT_ID of the tenant is 1002.

3. Obtain the backup path to be cleared.

   1. Query all backup paths of the specified tenant.

      obclient(root@sys)[(none)]> SELECT TENANT_ID, PATH, DEST_TYPE, STATUS FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE TENANT_ID = 1002 AND DEST_TYPE = 'backup_data';
      

      The query result is as follows:

      +-----------+-------------------------------+-------------+--------+
      | TENANT_ID | PATH                          | DEST_TYPE   | STATUS |
      +-----------+-------------------------------+-------------+--------+
      |      1002 | file:///data/nfs/backup/data2 | backup_data | NORMAL |
      |      1002 | file:///data/nfs/backup/data1 | backup_data | NORMAL |
      +-----------+-------------------------------+-------------+--------+
      2 rows in set
      

      From the query result, there are 2 backup paths for the tenant.

   2. Query the current backup path of the specified tenant.

      obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_BACKUP_PARAMETER WHERE TENANT_ID = 1002;
      

      The query result is as follows:

      +-----------+------------------+-------------------------------+
      | TENANT_ID | NAME             | VALUE                         |
      +-----------+------------------+-------------------------------+
      |      1002 | data_backup_dest | file:///data/nfs/backup/data2 |
      +-----------+------------------+-------------------------------+
      1 row in set
      

      From the query result, the current backup path is file:///data/nfs/backup/data2. Therefore, the backup path to be cleared, file:///data/nfs/backup/data1, is not the current backup path and can be cleared.

4. Execute the following command to clear the specified backup path.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST [=] 'data_backup_path' TENANT [=] tenant_name;
   

   where:

   • data_backup_path: specifies the data backup path to be cleared.

   • tenant_name: specifies the user tenant of the data backup path to be cleared.

   Here are some examples:

   OSS

   NFS

   The sys tenant clears the OSS backup path oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com of the mysql001 tenant.

   obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST ='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com' TENANT = mysql001;
   

   The sys tenant clears the NFS backup path file:///data/nfs/backup/data1 of the mysql001 tenant.

   obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST ='file:///data/nfs/backup/data1' TENANT = mysql001;
   

5. After the command is successfully executed, you can query the view to obtain the information about the backup path being cleared.

   obclient(root@sys)[(none)]> SELECT TENANT_ID, PATH, DEST_TYPE, STATUS FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE TENANT_ID = 1002 AND PATH = 'file:///data/nfs/backup/data1';
   

   The query result is as follows:

   +-----------+-------------------------------+-------------+----------+
   | TENANT_ID | PATH                          | DEST_TYPE   | STATUS   |
   +-----------+-------------------------------+-------------+----------+
   |      1002 | file:///data/nfs/backup/data1 | backup_data | DELETING |
   +-----------+-------------------------------+-------------+----------+
   1 row in set
   

   From the query result, you can see that the STATUS field of the backup path being cleared changes to DELETING. After the backup path is cleared, the corresponding row will be deleted.

Clear the backup path of the current user tenant

1. Log in to the database as the tenant administrator of the user tenant.

   Note

   The administrator of a MySQL-compatible tenant is the root user. The administrator of an Oracle-compatible tenant is the SYS user.

   The following example shows how to connect to the database. Please replace the actual environment variables.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. Obtain the backup path to be cleared.

   1. Query all backup paths of the current tenant.

      MySQL-compatible mode

      Oracle-compatible mode

      The following example shows how to query the backup paths of the current tenant in MySQL-compatible mode:

      obclient(root@mysql001)[(none)]> SELECT PATH, DEST_TYPE, STATUS FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE = 'backup_data';
      

      The following example shows how to query the backup paths of the current tenant in Oracle-compatible mode:

      obclient(SYS@oracle001)[SYS]> SELECT TENANT_ID, PATH, DEST_TYPE, STATUS FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE = 'backup_data'\G
      

      The query result is as follows:

      +-------------------------------+-------------+--------+
      | PATH                          | DEST_TYPE   | STATUS |
      +-------------------------------+-------------+--------+
      | file:///data/nfs/backup/data2 | backup_data | NORMAL |
      | file:///data/nfs/backup/data1 | backup_data | NORMAL |
      +-------------------------------+-------------+--------+
      2 rows in set
      

      From the query result, there are 2 backup paths for the tenant.

   2. Query the current backup path of the current tenant.

      MySQL-compatible mode

      Oracle-compatible mode

      The following example shows how to query the current backup path of the current tenant in MySQL-compatible mode:

      obclient(root@mysql001)[(none)]> SELECT * FROM oceanbase.DBA_OB_BACKUP_PARAMETER;
      

      The following example shows how to query the current backup path of the current tenant in Oracle-compatible mode:

      obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_BACKUP_PARAMETER;
      

      The query result is as follows:

      +------------------+-------------------------------+
      | NAME             | VALUE                         |
      +------------------+-------------------------------+
      | data_backup_dest | file:///data/nfs/backup/data2 |
      +------------------+-------------------------------+
      1 row in set
      

      From the query result, the current backup path is file:///data/nfs/backup/data2. Therefore, the backup path to be cleared, file:///data/nfs/backup/data1, is not the current backup path and can be cleared.

3. Execute the following command to clear the specified backup path.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST [=] 'data_backup_path';
   

   where data_backup_path specifies the data backup path to be cleared.

   Here are some examples:

   OSS

   NFS

   The user tenant clears the OSS backup path oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com of the current tenant.

   obclient> ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST ='oss://oceanbase-test-bucket/backup/data?host=****.aliyun-inc.com';
   

   The user tenant clears the NFS backup path file:///data/nfs/backup/data1 of the current tenant.

   obclient> ALTER SYSTEM DELETE BACKUP ALL DATA_BACKUP_DEST ='file:///data/nfs/backup/data1';
   

4. After the command is successfully executed, you can query the view to obtain the information about the backup path being cleared.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to query the backup path being cleared in MySQL-compatible mode:

   obclient(root@mysql001)[(none)]> SELECT PATH, DEST_TYPE, STATUS FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE PATH = 'file:///data/nfs/backup/data1';
   

   The following example shows how to query the backup path being cleared in Oracle-compatible mode:

   obclient(SYS@oracle001)[SYS]> SELECT PATH, DEST_TYPE, STATUS FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE PATH = 'file:///data/nfs/backup/data1';
   

   The query result is as follows:

   +-------------------------------+-------------+----------+
   | PATH                          | DEST_TYPE   | STATUS   |
   +-------------------------------+-------------+----------+
   | file:///data/nfs/backup/data1 | backup_data | DELETING |
   +-------------------------------+-------------+----------+
   1 row in set
   

   From the query result, you can see that the STATUS field of the backup path being cleared changes to DELETING. After the backup path is cleared, the corresponding row will be deleted.

Clear an archive path

Clear an archive path of a user tenant as the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

   The following example shows how to connect to the database. Replace the actual environment variables.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Query the tenant information to obtain the TENANT_ID of the tenant.

   The following example shows how to query the mysql001 tenant:

   obclient(root@sys)[(none)]> SELECT TENANT_ID, TENANT_NAME, TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql001';
   

   The query result is as follows:

   +-----------+-------------+-------------+
   | TENANT_ID | TENANT_NAME | TENANT_TYPE |
   +-----------+-------------+-------------+
   |      1002 | mysql001    | USER        |
   +-----------+-------------+-------------+
   1 row in set
   

   In this example, the TENANT_ID of the mysql001 tenant is 1002.

3. Obtain the archive path to be cleared.

   1. Query the oceanbase.CDB_OB_BACKUP_STORAGE_INFO view to obtain all archive paths of the tenant.

      obclient(root@sys)[(none)]> SELECT TENANT_ID, PATH, DEST_TYPE, STATUS FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE TENANT_ID = 1002 AND DEST_TYPE = 'archive_log';
      

      The query result is as follows:

      +-----------+----------------------------------+-------------+---------+
      | TENANT_ID | PATH                             | DEST_TYPE   | STATUS  |
      +-----------+----------------------------------+-------------+---------+
      |      1002 | file:///data/nfs/backup/archive2 | archive_log | NORMAL  |
      |      1002 | file:///data/nfs/backup/archive1 | archive_log | NORMAL  |
      +-----------+----------------------------------+-------------+---------+
      1 row in set
      

      In this example, the mysql001 tenant has two archive paths.

   2. Query the oceanbase.CDB_OB_ARCHIVE_DEST view to obtain the archive path in use.

      obclient(root@sys)[(none)]> SELECT TENANT_ID, NAME, VALUE FROM oceanbase.CDB_OB_ARCHIVE_DEST WHERE TENANT_ID = tenant_id AND NAME = 'path';
      

      The query result is as follows:

      +-----------+------+------------------------------------+
      | TENANT_ID | NAME | VALUE                              |
      +-----------+------+------------------------------------+
      |      1002 | path | file:///data/nfs/backup/archive2   |
      +-----------+------+------------------------------------+
      1 row in set
      

      In this example, the archive path in use is file:///data/nfs/backup/archive2, and the archive path to be cleared, file:///data/nfs/backup/archive1, is not in use.

4. Execute the following statement to clear the archive path.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST [=] 'log_archive_path' TENANT [=] tenant_name;
   

   The parameters are described as follows:

   • log_archive_path: specifies the archive path to be cleared.

   • tenant_name: specifies the user tenant whose archive path is to be cleared.

   The following examples show how to clear an archive path of the mysql001 tenant.

   OSS

   NFS

   Clear the OSS archive path oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com of the mysql001 tenant.

   obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST ='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com' TENANT = mysql001;
   

   Clear the NFS archive path file:///data/nfs/backup/archive1 of the mysql001 tenant.

   obclient(root@sys)[(none)]> ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST ='file:///data/nfs/backup/archive1' TENANT = mysql001;
   

5. After the command is successfully executed, you can query the information_schema.LOG_ARCHIVE_PATH view to view the archive path being cleared.

   obclient(root@sys)[(none)]> SELECT TENANT_ID, PATH, DEST_TYPE, STATUS FROM oceanbase.CDB_OB_BACKUP_STORAGE_INFO WHERE TENANT_ID = 1002 AND PATH = 'file:///data/nfs/backup/archive1';
   

   The query result is as follows:

   +-----------+----------------------------------+-------------+-----------+
   | TENANT_ID | PATH                             | DEST_TYPE   | STATUS    |
   +-----------+----------------------------------+-------------+-----------+
   |      1002 | file:///data/nfs/backup/archive1 | archive_log | DELETING  |
   +-----------+----------------------------------+-------------+-----------+
   1 row in set
   

   In the query result, the STATUS field of the archive path being cleared changes to DELETING. After the archive path is cleared, the corresponding row will be deleted.

Clear an archive path of the current user tenant

1. Log in to the database as the administrator of the user tenant.

   Note

   The administrator of a MySQL-compatible tenant is the root user. The administrator of an Oracle-compatible tenant is the SYS user.

   The following example shows how to connect to the database. Replace the actual environment variables.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A
   

2. Obtain the archive path to be cleared.

   1. Query the oceanbase.DBA_OB_BACKUP_STORAGE_INFO view to obtain all archive paths of the current tenant.

      MySQL-compatible mode

      Oracle-compatible mode

      The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in MySQL-compatible mode:

      obclient(root@mysql001)[(none)]> SELECT PATH, DEST_TYPE, STATUS FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE = 'archive_log';
      

      The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in Oracle-compatible mode:

      obclient(SYS@oracle001)[SYS]> SELECT PATH, DEST_TYPE, STATUS FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE DEST_TYPE = 'archive_log';
      

      The query result is as follows:

      +----------------------------------+-------------+---------+
      | PATH                             | DEST_TYPE   | STATUS  |
      +----------------------------------+-------------+---------+
      | file:///data/nfs/backup/archive2 | archive_log | NORMAL  |
      | file:///data/nfs/backup/archive1 | archive_log | NORMAL  |
      +----------------------------------+-------------+---------+
      2 rows in set
      

   2. Query the oceanbase.DBA_OB_ARCHIVE_DEST view to obtain the archive path in use.

      MySQL-compatible mode

      Oracle-compatible mode

      The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in MySQL-compatible mode:

      obclient(root@mysql001)[(none)]> SELECT NAME, VALUE FROM oceanbase.DBA_OB_ARCHIVE_DEST WHERE NAME = 'path';
      

      The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in Oracle-compatible mode:

      obclient(SYS@oracle001)[SYS]> SELECT NAME, VALUE FROM SYS.DBA_OB_ARCHIVE_DEST WHERE NAME = 'path';
      

      The query result is as follows:

      +------+------------------------------------+
      | NAME | VALUE                              |
      +------+------------------------------------+
      | path | file:///data/nfs/backup/archive2   |
      +------+------------------------------------+
      1 row in set
      

      In this example, the archive path to be cleared is not in use.

3. Execute the following statement to clear the archive path.

   The statement is as follows:

   ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST [=] 'log_archive_path';
   

   The log_archive_path parameter specifies the archive path to be cleared.

   The following examples show how to clear an archive path of the current user tenant.

   OSS

   NFS

   Clear the OSS archive path oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com of the current user tenant.

   obclient> ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST ='oss://oceanbase-test-bucket/backup/archive?host=****.aliyun-inc.com';
   

   Clear the NFS archive path file:///data/nfs/backup/archive1 of the current user tenant.

   obclient> ALTER SYSTEM DELETE BACKUP ALL LOG_ARCHIVE_DEST ='file:///data/nfs/backup/archive1';
   

4. After the command is successfully executed, you can query the view to view the archive path being cleared.

   MySQL-compatible mode

   Oracle-compatible mode

   The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in MySQL-compatible mode:

   obclient(root@mysql001)[(none)]> SELECT PATH, DEST_TYPE, STATUS FROM oceanbase.DBA_OB_BACKUP_STORAGE_INFO WHERE PATH = 'file:///data/nfs/backup/archive1';
   

   The following example shows how to query the information_schema.LOG_ARCHIVE_PATH view in Oracle-compatible mode:

   obclient(SYS@oracle001)[SYS]> SELECT PATH, DEST_TYPE, STATUS FROM SYS.DBA_OB_BACKUP_STORAGE_INFO WHERE PATH = 'file:///data/nfs/backup/archive1';
   

   The query result is as follows:

   +----------------------------------+-------------+-----------+
   | PATH                             | DEST_TYPE   | STATUS    |
   +----------------------------------+-------------+-----------+
   | file:///data/nfs/backup/archive1 | archive_log | DELETING  |
   +----------------------------------+-------------+-----------+
   1 row in set
   

   In the query result, the STATUS field of the archive path being cleared changes to DELETING. After the archive path is cleared, the corresponding row will be deleted.

References

• Query the backup cleanup progress

• Query the backup cleanup results

• Cancel a cleanup task