09 Move OMR - Fourth Attempt
08 Move OMR - Third Attempt
10 Move GORDON to VICTORIA

Contents

  1. 1 Overview
  2. 2 References
  3. 3 Prerequisite
  4. 4 Procedure
    1. 4.1 Drop Database
    2. 4.2 Create Database
    3. 4.3 Change Default Tablespace
    4. 4.4 Set Up Directory on EMREPOS
    5. 4.5 Create Database Link to EMREPOS
    6. 4.6 Transfer Files
    7. 4.7 Create Directory in EMREPOS
    8. 4.8 Update IMPDP Parameter
    9. 4.9 Copy Export Dump File to GORDON
    10. 4.10 Import Tranferred Metadata into EMREPOSP

Overview

So far, my attempts to move to the OMR from GRIDCTRL to GORDON have failed because of:

References

Prerequisite

According to the SQL Reference manual:

You can no longer change the database character set or the national character set using the ALTER DATABASE statement. Refer to Oracle Database Globalization Support Guide for information on database character set migration.

Procedure

Drop Database

Because I cannot change the character set of an existing database, I will have to drop it and recreate it.

Firstly, I used the srvctl command to drop the database as follows: 

srvctl remove database -db emrepos -verbose

The output was: 

Remove the database emrepos? (y/[n]) y
Successfully removed database and its dependent services.

Create Database

I followed the procedure in 02 Create EMREPOS Database Using DBCA , except for Step 2 where I made the following changes:

This time, there were no errors.

Change Default Tablespace

Because I am using transportable database to upgrade the 11.2 database to 12.1, I cannot have an existing USERS tablespace in the EMREPOS PDB because the tablespace already exists in the source database.

I used the following SQL*Plus commands to change the default tablespace: 

create tablespace default_user_ts;
alter database default tablespace default_user_ts;
drop tablespace users;

Set Up Directory on EMREPOS

Because the GUID of the PDB is different from last time, I will have to find the directory name for the data files from the V$DATAFILE view as I did before.

Note: files tranferred via DBMS_FILE_TRANSFER.PUT_FILE always end in the datafiles directory. It is only the alias that ends in the specified directory.

The database directory is created as follows: 

CREATE OR REPLACE DIRECTORY transfer AS '+DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE';

Create Database Link to EMREPOS

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

CONNECT SYSTEM
DROP DATABASE LINK emreposp;
CREATE DATABASE LINK emrepos
  connect to system identified by "&pw_system."
  using 'EMREPOS'
/

This was successful as shown by the following message: 

Database link dropped.
Database link created.

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

select host_name from v$instance@emrepos;

This was successful as shown by the following message: 

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

Transfer Files

Based on the code in 07 Move OMR - Second Attempt , I set up the following PL/SQL block in REPOS on GRIDCTRL : 

SET SERVEROUTPUT ON
DECLARE
  TYPE t_file_names IS TABLE OF VARCHAR2(256);
  l_file_names
    t_file_names 
      := t_file_names(
           'mgmt_deepdive.dbf',
           'mgmt_ecm_depot1.dbf',
           'mgmt.dbf',
           'rman_catalog.dbf',
           'users01.dbf');
  l_src_dir
    VARCHAR2(32) := 'DATA_FILES';
  l_dest_dir
    VARCHAR2(32) := 'TRANSFER';
  l_db_link
    VARCHAR2(32) := 'EMREPOS';
  l_file_name
    VARCHAR2(256);
BEGIN
  FOR l_idx in l_file_names.FIRST..l_file_names.LAST LOOP
    l_file_name  := l_file_names(l_idx);
    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 LOOP;
END;
/

This completed successfully. On GORDON , the files are listed as follows: 

[oracle@gordon ~]$ asmcmd ls -ls +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE
Type      Redund  Striped  Time             Sys  Block_Size  Blocks       Bytes       Space  Name
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   12801   104865792   106954752  DEFAULT_USER_TS.279.924127417
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192     641     5251072     6291456  FILE_TRANSFER.274.924255919
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   12801   104865792   106954752  FILE_TRANSFER.276.924255913
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192  787201  6448750592  6450839552  FILE_TRANSFER.277.924255563
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   25601   209723392   211812352  FILE_TRANSFER.278.924255553
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   25601   209723392   211812352  FILE_TRANSFER.280.924255539
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   66561   545267712   547356672  SYSAUX.271.923949763
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  Y          8192   33281   272637952   274726912  SYSTEM.272.923949761
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  N          8192  787201  6448750592  6450839552  mgmt.dbf => +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE/FILE_TRANSFER.277.924255563
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  N          8192   25601   209723392   211812352  mgmt_deepdive.dbf => +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE/FILE_TRANSFER.280.924255539
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  N          8192   25601   209723392   211812352  mgmt_ecm_depot1.dbf => +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE/FILE_TRANSFER.278.924255553
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  N          8192   12801   104865792   106954752  rman_catalog.dbf => +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE/FILE_TRANSFER.276.924255913
DATAFILE  UNPROT  COARSE   OCT 03 09:00:00  N          8192     641     5251072     6291456  users01.dbf => +DATA/GORDONC/3DB8402EC7AF38A5E0536B01A8C0A97C/DATAFILE/FILE_TRANSFER.274.924255919

Create Directory in EMREPOS

In the EMREPOS database on GORDON , a new database directory is created as follows for an existing host directory: 

CONNECT / AS SYSDBA
ALTER SESSION set container = emrepos;
CREATE DIRECTORY local_dp_dir AS '/opt/app/oracle/admin/emrepos/dpdump';

Update IMPDP Parameter

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

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

Copy Export Dump File to GORDON

On GRIDCTRL , the export dump file is copied to GORDON as follows: 

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

Import Tranferred Metadata into EMREPOSP

On GORDON , the impdp utility was invoked as follows: 

impdp system@emrepos parfile=move_omr.par

This failes. On GORDON , the import data-pump log shows: 

;;; 
Import: Release 12.1.0.2.0 - Production on Mon Oct 3 12:07:37 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;; 
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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@emrepos parfile=move_omr.par 
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE:"UNDOTBS1" failed to create with error:
ORA-01516: nonexistent log file, data file, or temporary file "/opt/oracle/app/oradata/repos/undotbs01.dbf"
Failing sql is:
 ALTER DATABASE DATAFILE '/opt/oracle/app/oradata/repos/undotbs01.dbf' RESIZE 933232640
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
ORA-39083: Object type PROFILE:"MGMT_INTERNAL_USER_PROFILE" failed to create with error:
ORA-07443: function MGMT_INTERNAL_PASS_VERIFY not found
Failing sql is:
 CREATE PROFILE "MGMT_INTERNAL_USER_PROFILE" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_TIME 
ORA-39083: Object type PROFILE:"MGMT_ADMIN_USER_PROFILE" failed to create with error:
ORA-07443: function MGMT_PASS_VERIFY not found
Failing sql is:
 CREATE PROFILE "MGMT_ADMIN_USER_PROFILE" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LIFE_TIME 15552000/86400 PASSWORD_REUSE_TIME UNLIMITED PASSWOR
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"ORDDATA" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SPATIAL_WFS_ADMIN_USR" already exists
ORA-31684: Object type USER:"SPATIAL_CSW_ADMIN_USR" already exists
ORA-31684: Object type USER:"FLOWS_FILES" already exists
ORA-31684: Object type USER:"APEX_PUBLIC_USER" already exists
ORA-39083: Object type USER:"SYSMAN" failed to create with error:
ORA-02380: profile MGMT_INTERNAL_USER_PROFILE does not exist
Failing sql is:
 CREATE USER "SYSMAN" IDENTIFIED BY VALUES 'S:DB388E97724A81674AE0839CB61F3B2D3342735E7CD8669CB7E4095D8AE8;3F6537D1D7F0974D' DEFAULT TABLESPACE "MGMT_TABLESPACE" TEMPORARY TABLESPACE "TEMP" PROFILE "MGMT_INTERNAL_USER_PROFILE"
ORA-39083: Object type USER:"SYSMAN_RO" failed to create with error:
ORA-02380: profile MGMT_INTERNAL_USER_PROFILE does not exist
Failing sql is:
 CREATE USER "SYSMAN_RO" IDENTIFIED BY VALUES 'S:09CC52ED94957917379715F3224A0C2BDD6AD34471098C4721E025C4D286;03EBCFFD46A35C71' DEFAULT TABLESPACE "MGMT_TABLESPACE" TEMPORARY TABLESPACE "TEMP" PROFILE "MGMT_INTERNAL_USER_PROFILE" PASSWORD EXPIRE ACCOUNT LOCK
ORA-39083: Object type USER:"MGMT_VIEW" failed to create with error:
ORA-02380: profile MGMT_INTERNAL_USER_PROFILE does not exist
Failing sql is:
 CREATE USER "MGMT_VIEW" IDENTIFIED BY VALUES 'S:A1372D27F2AF10CB5F4DF96918747B790A5C0805A724F47B83903969CA51;AB819F3F4E03F55E' DEFAULT TABLESPACE "MGMT_ECM_DEPOT_TS" TEMPORARY TABLESPACE "TEMP" PROFILE "MGMT_INTERNAL_USER_PROFILE"
ORA-39083: Object type USER:"SYSMAN_MDS" failed to create with error:
ORA-02380: profile MGMT_INTERNAL_USER_PROFILE does not exist
Failing sql is:
 CREATE USER "SYSMAN_MDS" IDENTIFIED BY VALUES 'S:EF8701B839156BF47ECC5FB35C0BF94FBD810C0058E8A69DFD63564CEBB2;84CDD161732D1867' DEFAULT TABLESPACE "MGMT_TABLESPACE" TEMPORARY TABLESPACE "TEMP" PROFILE "MGMT_INTERNAL_USER_PROFILE"

The problem stems from a password verification function that is owned by a schema that is being imported. The function cannot be created until the schema is created. The schema cannot be created until the profile is created. The profile cannot be created until the function is created.