07 Move OMR - Second Attempt

Overview

The first attempt to move the OMR from REPOS database on GRIDCTRL failed, the export data-dump failed.

Patch 17583185 has been applied to REPOS.

References

Procedure

Ensure Tablespaces Read-Only

In the REPOS database on GRIDCTRL, the non-administrative tablespaces should be in READ-ONLY mode. This is confimred as follows:

select tablespace_name, status from dba_tablespaces order by 1;

The output is:

TABLESPACE_NAME STATUS ------------------------------ --------- MGMT_AD4J_TS READ ONLY MGMT_ECM_DEPOT_TS READ ONLY MGMT_TABLESPACE READ ONLY RMAN_CATALOG READ ONLY SYSAUX ONLINE SYSTEM ONLINE TEMP ONLINE UNDOTBS1 ONLINE USERS READ ONLY 9 rows selected.

The tablespaces are in the correct state.

Create Export Dump File for Transportable Database

In the REPOS database on GRIDCTRL, the following export data-pump command was run to create a export dump file for transporting the database:

expdp system full=y dumpfile=move_omr.dmp directory=data_pump_dir transportable=always version=12 logfile=move_omr.log

This was successful as shown by the following messages:

****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /opt/oracle/app/admin/repos/dpdump/move_omr.dmp ****************************************************************************** Datafiles required for transportable tablespace MGMT_AD4J_TS: /opt/oracle/app/oradata/repos/mgmt_deepdive.dbf Datafiles required for transportable tablespace MGMT_ECM_DEPOT_TS: /opt/oracle/app/oradata/repos/mgmt_ecm_depot1.dbf Datafiles required for transportable tablespace MGMT_TABLESPACE: /opt/oracle/app/oradata/repos/mgmt.dbf Datafiles required for transportable tablespace RMAN_CATALOG: /opt/oracle/app/oradata/repos/rman_catalog.dbf Datafiles required for transportable tablespace USERS: /opt/oracle/app/oradata/repos/users01.dbf Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Sep 18 12:52:38 2016 elapsed 0 00:17:55

Create Directory for Data Files on REPOS

In the REPOS database on GRIDCTRL, the following database directory, DATA_FILES, was created based on the location of the data files given above:

create or replace directory data_files as '/opt/oracle/app/oradata/repos/';

This was successful as shown by the following message:

Directory created.

Create Directory for Data Files on EMREPOSP

In the EMREPOSP database on GORDON, the directory where the data files are stored was found as follows:

ALTER SESSION set container = emreposp; SELECT name FROM v$datafile;

The result was:

NAME -------------------------------------------------------------------------------- +DATA/EMREPOS/DATAFILE/undotbs1.265.900006519 +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/system.273.922824823 +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/sysaux.274.922824843

The following database directory, DATA_FILES, was created based on the location of the data files given above:

connect / as sysdba ALTER SESSION set container = emreposp; create or replace directory data_files as '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/';

This was successful as shown by the following message:

Directory created.

Set Up TNSNAMES for Remote Database on GRIDCTRL

On GRIDCTRL, the contents of the TNSNAMES file was set as follows:

cat >>/opt/oracle/app/OracleHomes/db11g/network/admin/tnsnames.ora <<DONE # ----------------------------------------------------------------------------- # TNS Names entries # ----------------------------------------------------------------------------- emrepos = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=gordon.yaocm.id.au)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=emrepos.yaocm.id.au)(SERVER=DEDICATED)) ) emreposp = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=gordon.yaocm.id.au)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=emreposp.yaocm.id.au)(SERVER=DEDICATED)) ) DONE

Create Database Link to EMREPOSP

In the REPOS database on GRIDCTRL as the SYSTEM user, the following database link was created:

CONNECT SYSTEM create database link emreposp connect to system identified by "&pw_system." using 'EMREPOSP' /

This was successful as shown by the following message:

Database link created.

And the following SQL statement shows that the connection was successful:

select host_name from v$instance@emreposp;

This was successful as shown by the following message:

HOST_NAME ---------------------------------------------------------------- gordon.yaocm.id.au

Transfer Files

In the REPOS database on GRIDCTRL as the SYSTEM user, the following PL/SQL block was used to transfer the data files to EMPREPOSP database on GORDON:

SET SERVEROUTPUT ON DECLARE l_src_dir VARCHAR2(32) := 'DATA_FILES'; l_dest_dir VARCHAR2(32) := 'DATA_FILES'; l_db_link VARCHAR2(32) := 'EMREPOSP'; l_file_name VARCHAR2(256); BEGIN l_file_name := 'mgmt_deepdive.dbf'; DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => l_src_dir, source_file_name => l_file_name, destination_directory_object => l_dest_dir, destination_file_name => l_file_name, destination_database => l_db_link); l_file_name := 'mgmt_ecm_depot1.dbf'; DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => l_src_dir, source_file_name => l_file_name, destination_directory_object => l_dest_dir, destination_file_name => l_file_name, destination_database => l_db_link); l_file_name := 'mgmt.dbf'; DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => l_src_dir, source_file_name => l_file_name, destination_directory_object => l_dest_dir, destination_file_name => l_file_name, destination_database => l_db_link); l_file_name := 'rman_catalog.dbf'; DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => l_src_dir, source_file_name => l_file_name, destination_directory_object => l_dest_dir, destination_file_name => l_file_name, destination_database => l_db_link); l_file_name := 'users01.dbf'; DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => l_src_dir, source_file_name => l_file_name, destination_directory_object => l_dest_dir, destination_file_name => l_file_name, destination_database => l_db_link); END; /

The output was:

PL/SQL procedure successfully completed.

Verify Transferred Files

On GORDON, the contents of the destination directory were displayed using the following command:

asmcmd ls -ls +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE

The output was:

Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name DATAFILE UNPROT COARSE SEP 18 16:00:00 Y 8192 25601 209723392 211812352 FILE_TRANSFER.276.922897989 DATAFILE UNPROT COARSE SEP 18 16:00:00 Y 8192 25601 209723392 211812352 FILE_TRANSFER.277.922898001 DATAFILE UNPROT COARSE SEP 18 16:00:00 Y 8192 787201 6448750592 6450839552 FILE_TRANSFER.278.922898013 DATAFILE UNPROT COARSE SEP 18 16:00:00 Y 8192 12801 104865792 106954752 FILE_TRANSFER.279.922898345 DATAFILE UNPROT COARSE SEP 18 16:00:00 Y 8192 641 5251072 6291456 FILE_TRANSFER.280.922898353 DATAFILE UNPROT COARSE SEP 18 14:00:00 Y 8192 74241 608182272 610271232 SYSAUX.274.922824843 DATAFILE UNPROT COARSE SEP 18 14:00:00 Y 8192 33281 272637952 274726912 SYSTEM.273.922824823 DATAFILE UNPROT COARSE SEP 18 16:00:00 N 8192 787201 6448750592 6450839552 mgmt.dbf => +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/FILE_TRANSFER.278.922898013 DATAFILE UNPROT COARSE SEP 18 16:00:00 N 8192 25601 209723392 211812352 mgmt_deepdive.dbf => +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/FILE_TRANSFER.276.922897989 DATAFILE UNPROT COARSE SEP 18 16:00:00 N 8192 25601 209723392 211812352 mgmt_ecm_depot1.dbf => +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/FILE_TRANSFER.277.922898001 DATAFILE UNPROT COARSE SEP 18 16:00:00 N 8192 12801 104865792 106954752 rman_catalog.dbf => +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/FILE_TRANSFER.279.922898345 DATAFILE UNPROT COARSE SEP 18 16:00:00 N 8192 641 5251072 6291456 users01.dbf => +DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/FILE_TRANSFER.280.922898353

Create Data Pump Directory for EMREPOSP

On GORDON, the directory was created as follows:

mkdir -p /opt/app/oracle/admin/emreposp/dpdump

In the EMREPOSP database on GORDON, the data pump directory was created as follows:

connect / as sysdba ALTER SESSION set container = CDB$ROOT; CREATE OR REPLACE DIRECTORY data_pump_dir AS '/opt/app/oracle/admin/emreposp/dpdump';

Transfer Export Dump File to GORDON

On GRIDCTRL, the export dump file created above was copied to GORDON as follows:

scp /opt/oracle/app/admin/repos/dpdump/move_omr.dmp gordon:/opt/app/oracle/admin/emreposp/dpdump

The output was:

The authenticity of host 'gordon (192.168.1.107)' can't be established. RSA key fingerprint is 6c:ac:10:f8:33:ae:ce:58:1a:79:64:4d:a6:7a:bb:7f. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'gordon' (RSA) to the list of known hosts. oracle@gordon's password: move_omr.dmp 100% 382MB 38.2MB/s 00:10

Set Up TNSNAMES on GORDON

On GORDON, the contents of the TNSNAMES file was set as follows:

cat >>//opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora <<DONE # ----------------------------------------------------------------------------- # TNS Names entries # ----------------------------------------------------------------------------- emrepos = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=gordon.yaocm.id.au)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=emrepos.yaocm.id.au)(SERVER=DEDICATED)) ) emreposp = (DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=gordon.yaocm.id.au)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=emreposp.yaocm.id.au)(SERVER=DEDICATED)) ) DONE

Create Import Data Pump Parameter File

On GORDON, a parameter file for the impdp utility was created as follows:

cat >move_omr.par <<DONE FULL=Y DUMPFILE=move_omr.dmp DIRECTORY=data_pump_dir TRANSPORT_DATAFILES= '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/mgmt.dbf', '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/mgmt_deepdive.dbf', '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/mgmt_ecm_depot1.dbf', '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/rman_catalog.dbf', '+DATA/EMREPOS/3CB2554A25C20D21E0536B01A8C0F6BF/DATAFILE/users01.dbf' LOGFILE=import_omr.log DONE

Import Transferred Metadata into EMREPOS

On GORDON, impdp utility was invoked to import the transferred database:

impdp system@emreposp parfile=move_omr.par

This failed with the following error messages:

Import: Release 12.1.0.2.0 - Production on Sun Sep 18 19:25:13 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name DATA_PUMP_DIR is invalid