# oceanbase-recovery

Part of **OCEANBASE**

<!-- intent-backlink:auto -->

> 💡 **Path Selection**: This skill is one implementation path for [Import or export data to/from OceanBase](../../intent/oceanbase-import-data/SKILL.md). If you're unsure which path to take, check the routing skill first.

# OceanBase Backup, Recovery, and Migration

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Query Historical Data by Timestamp or SCN | Synchronous | Use flashback queries to access data as it existed at a previous point in time. |
| Import/Export Data via DataX | Synchronous | Use DataX reader and writer plugins to move data between OceanBase and other data sources like CSV files. |

## API Calling Patterns

### Authentication
OceanBase backup and recovery operations do not use standard API keys or bearer tokens. Instead:

- **Flashback queries** are executed directly via SQL over an existing database connection (e.g., using `obclient`). No additional authentication is required beyond standard database login.
- **DataX jobs** authenticate using database credentials (`username` and `password`) embedded in the JSON configuration file. The password is typically masked (e.g., `**1***`) in examples.

Environment variables are not used for authentication in these workflows. Credentials must be provided in the connection context (SQL session or DataX config).

### Service Endpoint
There is no centralized REST API endpoint for these features:

- **Flashback queries** are run against the OceanBase database instance itself using standard SQL clients (e.g., `obclient` connected to port 2883).
- **DataX** is a standalone Java tool that connects to OceanBase via JDBC URL patterns like:
```text
  jdbc:oceanbase://<host>:<port>/<database>
  ```
  Common deployment regions include `cn-hangzhou`, `cn-shanghai`, and `cn-beijing`, but the actual endpoint depends on your OceanBase cluster’s network configuration.

### Synchronous Pattern
Both supported functions operate synchronously:

- **Flashback queries**: Submit a `SELECT ... AS OF [TIMESTAMP \| SCN]` statement and receive results immediately if within the `undo_retention` window.
- **DataX jobs**: Run `bin/datax.py <config.json>`; the process blocks until data transfer completes or fails.

No asynchronous polling, streaming, or WebSocket patterns are used in this domain.

## Parameter Reference

### Query Historical Data by Timestamp or SCN

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| undo_retention | integer | No | 0 | Non-negative integer | Sets the retention period for undo data in seconds. Must be configured before flashback queries can access historical data. |

> Note: The actual query uses either a timestamp expression or an SCN value directly in the SQL syntax (e.g., `AS OF TIMESTAMP ...` or `AS OF SCN ...`), not as named parameters.

### Import/Export Data via DataX

#### Reader / Writer Plugin Parameters

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| name | string | Yes | — | Valid plugin name | Plugin identifier (e.g., `oceanbasev10reader`, `txtfilewriter`). |
| username | string | Yes | — | — | Database username for authentication. |
| password | string | Yes | — | — | Password for the database user. |
| jdbcUrl | string | Yes | — | Valid JDBC URL | Connection string to OceanBase instance. |
| table | string | Yes | — | — | Name of the source/target table. |
| column | array | Yes | — | List of column names | Columns to read or write. |
| readBatchSize | integer | No | 10000 | ≤ 100000 | Number of rows to read per batch. |
| writeMode | string | No | insert | One of: insert / truncate / upsert | How to handle existing data during write. |
| batchSize | integer | No | 1024 | ≤ 1024 | Records processed per write batch. |
| timeout | integer | No | 10000 | 1000 ≤ value ≤ 30000 | Operation timeout in milliseconds. |
| fieldDelimiter | string | No | `|| ` / — / Delimiter for text file fields. |
| path | string or array | No | — | — | Input/output file path(s). |
| fileName | string | No | — | — | Output file name (for writers). |
| charset | string | No | UTF-8 | One of: UTF-8 / GBK | Character encoding for files. |
| dateFormat | string | No | yyyy-MM-dd | Format: yyyy-MM-dd | Date parsing format. |
| nullFormat | string | No | "" | — | String representing NULL values. |
| splitPk | string | No | — | — | Primary key for parallel data splitting. |
| activeMemPercent | string | No | "90" | 0–100% | Active memory usage threshold. |
| memstoreThreshold | string | No | "90" | 0–100% | Memstore flush threshold. |
| readByPartition | boolean | No | true | — | Whether to read by partition. |
| where | string | No | "" | — | SQL WHERE clause for filtering. |
| preSql | array | No | [] | — | SQL statements to run before writing. |

## Code Examples

### Flashback Query by SCN - SQL - All Regions

```sql
obclient> SELECT * FROM tbl1 AS of scn 1582807800000000;
```

### Flashback Query by Timestamp - SQL - All Regions

```sql
obclient> SELECT * FROM tbl1 AS of timestamp TO_TIMESTAMP('2020-08-13 16:20:00','yyyy-mm-dd hh24:mi:ss');
```

### Export OceanBase Table to CSV - Bash - All Regions

```bash
[admin@*** /home/admin/datax3]
$cat job/ob_tpcc_ware_2_csv.json
{
        "job":{
                "setting":{
                        "speed":{
                                "channel":10
                        },
                        "errorLimit":{
                                "record":0, "percentage": 0.02
                        }
                },
                "content":[
                        {
                                "reader":{
                                        "name":"oceanbasev10reader",
                                        "parameter":{
                                                "where":"",
                                                "timeout":10000,
                                                "readBatchSize":100000,
                                                "readByPartition":"true",
                                                "column": [
"W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
                        ],
                                                "connection":[
                                                        {
                                                                "jdbcUrl":["||_dsc_ob10_dsc_||obdemo:obbmsql||_dsc_ob10_dsc_||jdbc:oceanbase://127.1:2883/tpcc"],
                                                                "table":["ware"]
                                                        }
                                                ],
                                                "username":"tpcc",
                                                "password":"**1***"
                                        }
                                },
                                "writer":{
                                        "name":"txtfilewriter",
                                        "parameter":{
                                                "path":"/home/admin/csvdata/",
                                                "fileName":"ware",
                                                "writeMode":"truncate",
                                                "dateFormat":"yyyy-MM-dd",
                                                "charset":"UTF-8",
                                                "nullFormat":"",
                                                "fileDelimiter":"||"
                                        }
                                }
                        }
                ]
        }
}

[admin@*** /home/admin/datax3]
$bin/datax.py job/ob_tpcc_ware_2_csv.json
```

### Import CSV to OceanBase Table - Bash - All Regions

```bash
[admin@*** /home/admin/datax3]
$cat job/csv_2_ob_tpcc_ware2.json
{
    "job":{
        "setting":{
            "speed":{
                "channel":32
            },
            "errorLimit":{
                "record":0, "percentage": 0.02
            }
        },
        "content":[
            {
                "reader":{
                    "name":"txtfilereader",
                    "parameter":{
                        "path":["/home/admin/csvdata/ware*"],
                        "encoding":"UTF-8",
                        "column":[
                            {    "index":0,    "type":"long"    }
                            ,{    "index":1,    "type":"long"    }
                            ,{    "index":2,    "type":"long"    }
                            ,{    "index":3,    "type":"string"    }
                            ,{    "index":4,    "type":"string"    }
                            ,{    "index":5,    "type":"string"    }
                            ,{    "index":6,    "type":"string"    }
                            ,{    "index":7,    "type":"string"    }
                            ,{    "index":8,    "type":"string"    }
                        ],
                        "fieldDelimiter":",",
                        "fileFormat":"text"
                    }
                },
                "writer":{
                    "name":"oceanbasev10writer",
                    "parameter":{
                        "writeMode":"insert",
                        "column":[
                                "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
                        ],
                        "connection":[
                            {
"jdbcUrl":"||_dsc_ob10_dsc_||obdemo:obbmsql||_dsc_ob10_dsc_||jdbc:oceanbase://127.1:2883/tpcc",
                                "table":["WARE2"]
                            }
                        ],
                        "username":"tpcc",
                        "password":"**1***",
                        "batchSize":256,
                        " memstoreThreshold":"90"
                    }
                }
            }
        ]
    }
}
```

### Set Undo Retention Globally - SQL - All Regions

```sql
obclient> SET GLOBAL undo_retention=900;
```

## Error Handling

| Error Code | Description | Recommended Action |
|-----------|-------------|-------------------|
| 400 | Bad request – invalid configuration or malformed JSON. | Validate DataX JSON syntax and required fields (e.g., `name`, `parameter`, `username`). |
| 500 | Internal server error – unexpected issue during execution. | Check OceanBase logs; ensure cluster is healthy and tables exist. |
| ConnectionError | Failed to connect to the database due to network issues or incorrect credentials. | Verify JDBC URL, host/port accessibility, and username/password correctness. |
| TimeoutError | Operation timed out; increase the timeout value or check network latency. | Increase the `timeout` parameter (default 10000 ms) in DataX configuration. |

## FAQ

Q: How far back can I query historical data using flashback?
A: It depends on the `undo_retention` setting (in seconds) and whether major compactions have overwritten the required data blocks. By default, `undo_retention=0`, which disables historical access. Set it to a positive value (e.g., 900 for 15 minutes) before performing critical operations.

Q: Can I use DataX to migrate data between OceanBase and MySQL?
A: Yes. Use `oceanbasev10reader` with OceanBase JDBC URL and `mysqlwriter` (or vice versa) with appropriate connection parameters. Ensure compatible data types and handle schema differences manually.

Q: Why does my flashback query return no data even with a valid timestamp?
A: The requested snapshot may fall outside the `undo_retention` window, or a major compaction may have purged the necessary undo logs. Check `v$ob_timestamp_service` to verify available SCN/timestamp ranges.

Q: Do I need to install anything to use DataX?
A: Yes. DataX is a standalone Java application. Download it from the official repository, ensure Java 8+ is installed, and configure plugins (like `oceanbasev10reader`) in the `plugin/` directory.

Q: Is there a REST API for triggering backups or recoveries?
A: Not in this skill scope. OceanBase provides SQL-based flashback and log-based recovery. Full backup/restore operations are typically managed via OCP (OceanBase Cloud Platform) or command-line tools like `ob_admin`, not public REST APIs.

## Pricing & Billing

### Billing Model
Flashback queries are included in standard OceanBase licensing with no additional charges. DataX is an open-source tool; its usage incurs no direct fees, though underlying infrastructure (compute, storage, network) is billed separately.

### Free Tier
No explicit cost is mentioned for flashback queries; the feature is part of core OceanBase functionality.

### Usage Limits
Flashback query availability is limited by:
- `undo_retention` duration (must be explicitly configured)
- Major compaction cycles (which may purge old versions)

DataX performance is constrained by:
- `readBatchSize` (max 100,000)
- `batchSize` (max 1,024)
- Network and database load

### Billing Notes
No additional charges apply for using flashback queries or DataX. Costs are tied to your OceanBase cluster’s resource consumption (CPU, memory, storage I/O).