Oracle 数据库离线迁移至 OceanBase 数据库 Oracle 模式的参考手册

SELECT owner,type,name,referenced_owner,referenced_type,referenced_name
FROM dba_dependencies t
WHERE owner IN ('ALVIN');

SELECT owner,type,name,referenced_owner,referenced_type,referenced_name
FROM dba_dependencies t
WHERE owner IN ('ALVIN')
and owner <> referenced_owner
-- and referenced_type not in ('TABLE','SEQUENCE')
-- and REFERENCED_OWNER  in ('ALVIN')
ORDER BY owner,type,name,referenced_owner,referenced_type,referenced_name;

CREATE USER SettleAdmin IDENTIFIED BY ALVIN;
GRANT CONNECT TO ALVIN;
GRANT RESOURCE TO ALVIN;
GRANT CREATE VIEW TO ALVIN;
GRANT DBA TO ALVIN;
GRANT SELECT ANY TABLE TO ALVIN;
GRANT SELECT ANY DICTIONARY TO ALVIN;
GRANT SELECT_CATALOG_ROLE TO ALVIN;

SELECT value FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET';

./dbcat convert -H 10.10.20.20 -P 1521 -u alvin -p alvin --service-name orcl -D alvin --from oracle11g --to oboracle420  --all

ls -1 xxx/dbcat-2.1.0-RELEASE/output/dbcat-2020-10-10-202000/ALVIN
FUNCTION-schema.sql
PACKAGE BODY-schema.sql
PACKAGE-schema.sql
SEQUENCE-schema.sql
TABLE-schema.sql
TYPE BODY-schema.sql
TYPE-schema.sql
VIEW-schema.sql

unzip ob-loader-dumper-4.2.x-RELEASE.zip
cd ob-loader-dumper-4.2.x-RELEASE

DB_HOST=10.10.20.21
DB_PORT=2883
DB_USER=ALVIN@oboracle#obcluster
DB_PASSWORD=xxx
DB_NAME=alvin

cd ob-loader-dumper-4.2.x-RELEASE/

DB_OUTPUT=./output/dbcat-2020-10-10-202000/ALVIN/"PACKAGE BODY-schema.sql"
LOG_PATH=./output/20200101_1_ALVIN_log_TABLE/
mkdir -p "${LOG_PATH}"
./bin/obloader -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p ${DB_PASSWORD} -D ${DB_NAME} -f "${DB_OUTPUT}" --log-path "${LOG_PATH}" --mix --all

TIME_FORMAT_w='+%Y%m%d_%w_%H%M%S'
TIME_STR="$(date "${TIME_FORMAT_w}")"

DB_TYPE=OceanBase
DB_HOST=10.10.20.20
DB_PORT=2883
DB_USER=SYS@oboracle#obcluster
DB_PASSWORD=xxx
DB_NAME=SYS

SQL_FILE=xxx.sql
obclient -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c -e "source ${SQL_FILE}" &> "${DB_TYPE}_${DB_NAME}_${SQL_FILE}_${TIME_STR}.log"; wc -l "${DB_TYPE}_${DB_NAME}_${SQL_FILE}_${TIME_STR}.log"

SQL_FILE=yyy.sql
obclient -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c -e "source ${SQL_FILE}" &> "${DB_TYPE}_${DB_NAME}_${SQL_FILE}_${TIME_STR}.log"; wc -l "${DB_TYPE}_${DB_NAME}_${SQL_FILE}_${TIME_STR}.log"

...

SELECT
  t.owner,
  t.object_type,
  t.status,
  COUNT(1)
FROM
  dba_objects t
where
  t.owner IN (
    'ALVIN'
  )
GROUP BY
  t.owner,
  t.object_type,
  t.status
ORDER BY
  t.owner,
  t.object_type,
  t.status;

SELECT
  t.owner,
  t.object_type,
  t.status,
  t.object_name
FROM
  dba_objects t
where
  t.owner IN (
    'ALVIN'
  )
  and t.status IN ('INVALID')
ORDER BY
  t.owner,
  t.object_type,
  t.status;

ALTER PACKAGE pkg_test_compile COMPILE SPECIFICATION;
ALTER PACKAGE pkg_test_compile COMPILE BODY;
ALTER PACKAGE pkg_test_compile COMPILE;

SELECT * FROM user_errors;

SET SERVEROUTPUT ON;

BEGIN
  FOR obj IN (SELECT object_name, object_type
              FROM user_objects
              WHERE object_type IN ('PACKAGE','PACKAGE BODY','TRIGGER','PROCEDURE','FUNCTION','TYPE') 
              AND status = 'INVALID' )
  LOOP
    BEGIN

      IF obj.object_type = 'PACKAGE' THEN
              dbms_output.put_line('ALTER PACKAGE ' || obj.object_name || ' COMPILE SPECIFICATION;');
      ELSIF obj.object_type = 'PACKAGE BODY' THEN
              dbms_output.put_line('ALTER PACKAGE ' || obj.object_name || ' COMPILE BODY;');
      ELSE
              dbms_output.put_line('ALTER ' || obj.object_type || ' ' || obj.object_name || ' COMPILE;');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('Failed to compile ' || obj.object_type || ' "' || obj.object_name || '": ' || SQLERRM);
    END;
  END LOOP;
END;
/

SELECT * FROM user_errors;

SELECT * FROM user_errors;

SELECT * FROM all_errors WHERE owner = 'ALVIN';

cd ob-loader-dumper-4.2.x-RELEASE/

DB_OUTPUT=./output/data/ALVIN/"data.sql"
LOG_PATH=./output/20200101_1_ALVIN_log_data/
mkdir -p "${LOG_PATH}"
./bin/obloader -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p ${DB_PASSWORD} -D ${DB_NAME} -f "${DB_OUTPUT}" --log-path "${LOG_PATH}" --mix --all

 <dependency> 

  <groupId>com.oceanbase</groupId> 

  <artifactId>oceanbase-client</artifactId> 

  <version>2.4.11</version> 

 </dependency>

url=jdbc:oceanbase://10.x.x.x:2883/alvin?usePieceData=true&rewriteBatchedStatements=true&allowMultiQueries=true&useServerPrepStmts=true&useCursorFetch=true&defaultFetchSize=100&socketTimeout=3600000&connectTimeout=60000

username=ALVIN@oboracle#obcluster

password=xxx

driver-class-name=com.oceanbase.jdbc.Driver