데이터베이스 전체 이행

SELECT banner FROM v$version;
SELECT name, cdb, open_mode FROM v$database;
SELECT parameter, value FROM nls_database_parameters
 WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
SELECT filename, version FROM v$timezone_file;
SELECT comp_id, version, status FROM dba_registry ORDER BY comp_id;

SELECT username, default_tablespace, account_status
  FROM dba_users WHERE oracle_maintained = 'N' ORDER BY username;
SELECT owner, ROUND(SUM(bytes)/1024/1024) AS segment_mb
  FROM dba_segments
 WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
 GROUP BY owner ORDER BY owner;

CREATE DIRECTORY dpump_db AS '/home/oracle/dpump_db';
GRANT READ, WRITE ON DIRECTORY dpump_db TO system;

expdp system FULL=Y DIRECTORY=DPUMP_DB ESTIMATE_ONLY=Y LOGFILE=db_full_estimate.log

expdp system FULL=Y \
  DIRECTORY=DPUMP_DB \
  DUMPFILE=db_full_12c.dmp \
  LOGFILE=db_full_12c_exp.log \
  FLASHBACK_TIME=SYSTIMESTAMP \
  LOGTIME=ALL METRICS=Y
tail -n 40 /home/oracle/dpump_db/db_full_12c_exp.log
sha256sum /home/oracle/dpump_db/db_full_12c.dmp
scp /home/oracle/dpump_db/db_full_12c.dmp oracle@<TARGET_HOST>:/home/oracle/dpump_db/

impdp system FULL=Y DIRECTORY=DPUMP_DB DUMPFILE=db_full_12c.dmp \
  SQLFILE=db_full_preview.sql LOGFILE=db_full_preview.log
grep -nE 'CREATE.*TABLESPACE|\.dbf' /home/oracle/dpump_db/db_full_preview.sql

FULL=Y
DIRECTORY=DPUMP_DB
DUMPFILE=db_full_12c.dmp
LOGFILE=db_full_19c_imp.log
LOGTIME=ALL
METRICS=Y
REMAP_DATAFILE="'<SOURCE_DATA_DIR>/<FILE>.dbf':'<TARGET_DATA_DIR>/<FILE>.dbf'"

impdp system PARFILE=/home/oracle/dpump_db/db_full_imp.par SQLFILE=db_full_preview_final.sql
impdp system PARFILE=/home/oracle/dpump_db/db_full_imp.par
grep -n 'ORA-' /home/oracle/dpump_db/db_full_19c_imp.log

impdp system DIRECTORY=DPUMP_DB DUMPFILE=db_full_12c.dmp \
  SCHEMAS=<SCHEMA1>,<SCHEMA2> TABLE_EXISTS_ACTION=REPLACE \
  LOGFILE=db_schema_replace.log LOGTIME=ALL METRICS=Y

SELECT grantee, privilege, admin_option FROM dba_sys_privs WHERE grantee IN ('<SCHEMA1>', '<SCHEMA2>');
SELECT grantee, owner, table_name, privilege FROM dba_tab_privs WHERE grantee IN ('<SCHEMA1>', '<SCHEMA2>');
SELECT grantee, granted_role, default_role FROM dba_role_privs WHERE grantee IN ('<SCHEMA1>', '<SCHEMA2>');

SELECT owner, object_type, status, COUNT(*) AS cnt
  FROM dba_objects
 WHERE owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N')
 GROUP BY owner, object_type, status ORDER BY owner, object_type, status;

SELECT owner, object_name, object_type FROM dba_objects
 WHERE status = 'INVALID'
   AND owner IN (SELECT username FROM dba_users WHERE oracle_maintained = 'N');

SELECT COUNT(*) FROM <OWNER>.<TABLE_NAME>;

ALTER VIEW <OWNER>.<VIEW_NAME> COMPILE;
SHOW ERRORS VIEW <OWNER>.<VIEW_NAME>
@?/rdbms/admin/utlrp.sql