Embedded Files
Overview
Overview
So far, my attempts to move to the OMR from GRIDCTRL to GORDON have failed because of:
- Bug EXPDP - ORA-39097 ORA-39065 ORA-00911 (invalid Character) With FULL=Y TRANSPORTABLE=Y (Doc ID 1951858.1)
- Incorrect directory
- Mismatch of character sets
References
References
- Oracle® 12.1 Database SQL Language Reference
- Oracle® 12.1 Database Globalization Support Guide
- Oracle® 11.2 Database PL/SQL Packages and Types Reference
Prerequisite
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
Procedure
Drop Database
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
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
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
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
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
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
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
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
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
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.
Page updated
Google Sites
Report abuse