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
- Oracle ® 12.1 Database Administrator's Guide
- Oracle ® 11.2 Database PL/SQL Packages and Types Reference
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