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.
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.
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
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.
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.
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
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
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.
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
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';
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
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
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
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