Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of Oracle data migration". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The environment for this article all uses the same operating system: Oracle Linux 7.5. 11g data files are stored on the file system, while 19c data files are stored on ASM, in which NFS is used to temporarily store the data files, and then migrated through the transferable tablespace feature of rman.

1. Source-side check

Since rman cannot automatically migrate directories, external tables, and BFILEs, you must check it using the following command and then manually create it on the target side:

[oracle@rhndb ~] $sqlplus "/ as sysdba" SQL > set serveroutput on;SQL > declare x boolean;begin x SQL. Checkoutside outside end; 2 / The following directories exist in the database:SYS.DMP, SYS.XMLDIR, SYS.ORACLE_OCM_CONFIG_DIR2, SYS.ORACLE_OCM_CONFIG_DIR,SYS.DATA_PUMP_DIRPL/SQL procedure successfully completed.SQL > set linesize 300SQL > col directory_name for a25SQL > col directory_path for a70SQL > select directory_name,directory_path from dba_directories 2. Restart the database to read-only status SQL > shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL > startup mountORACLE instance started.Total System Global Area 3206836224 bytesFixed Size 2257520 bytesVariable Size 738200976 bytesDatabase Buffers 2449473536 bytesRedo Buffers 16904192 bytesDatabase mounted.SQL > alter database open read only;Database altered.3, DBMS_TDB.CHECK_DB check database status SQL > set serveroutput on;SQL > declare db_ready boolean 2 begin 3 db_ready: = dbms_tdb.check_db (4 end; 5 / PL/SQL procedure successfully completed.4, listing the data files that need and do not need to be converted SQL > select file_name "Datafiles requiring Conversion" from dba_data_files where tablespace_name in (select distinct tablespace_name from dba_rollback_segs) Datafiles requiring Conversion---/u02/oradata/rhndb/undotbs01.dbf/u02/oradata/rhndb/system01.dbfSQL > select file_name "Files NOT requiring Conversion" from dba_data_files where tablespace_name not in (select distinct tablespace_name from dba_rollback_segs) Files NOT requiring Conversion---/u02/oradata/rhndb/users01.dbf/u02/oradata/rhndb/sysaux01.dbf/u02/oradata/rhndb/spw01.dbf5, copy the data files of the source database to the destination side

The target side here uses ASM, so it cannot be stored directly. Therefore, the NFS file system is used to temporarily store the source-side data files.

[oracle@rhndb ~] $cp / u02 create pfile='/tmp/initrhndb.ora' from spfile cp * / u03/orabak6, create the target library parameter file and launch to oradata > oradata [oracle@rhndb ~] $scp / tmp/initrhndb.ora db02:$ORACLE_HOME/dbs-- modify audit_file_dest,control_files,db_name,db_recover_file_dest in parameter file Diagnostic_ destinations [oracle @ rhndb ~] $vi $ORACLE_HOME/dbs/initrhndb.orarhndb.__db_cache_size=2516582400rhndb.__java_pool_size=16777216rhndb.__large_pool_size=33554432rhndb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentrhndb.__pga_aggregate_target=1073741824rhndb.__sga_target=3221225472rhndb.__shared_io_pool_size=0rhndb.__shared_pool_size=620756992rhndb.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/ Admin/rhndb/adump'*.audit_trail='db'*.compatible='11.2.0.4.0'*.control_files='+DATA/rhndb/controlfile/control01.ctl' '+ FRA/rhndb/controlfile/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='rhndb'*.db_recovery_file_dest='+FRA'*.db_recovery_file_dest_size=4385144832*.diagnostic_dest='/u01/app/oracle'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=rhndbXDB)' * .open _ cursors=300*.pga_aggregate_target=1073741824*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=3221225472*.undo_tablespace='UNDOTBS1'* Nomount status at allow_resetlogs_corruption=true-- startup [oracle@db02 ~] $sqlplus "/ as sysdba" SQL > startup nomount pfile=/tmp/initrhndb.oraORACLE instance started.Total System Global Area 3221222464 bytesFixed Size 8901696 bytesVariable Size 671088640 bytesDatabase Buffers 2533359616 bytesRedo Buffers 7872512 bytes7, Data file conversion

A conversion operation is required regardless of whether the endian format of the two platforms is the same or not. According to the information in step 4, perform the conversion operation in rman, as follows:

[oracle@db02 ~] $rman target /-- conversion operation RMAN > convert from platform 'Linux x86 64 format' parallelism 22 > datafile'/ u03 orabakActionsystem01.dbf' format'+ data'3 > datafile'/ u03 orabakActionUndotbs01.dbf' format'+ data' Starting conversion at target at 28-APR-2019 19:03:38using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=135 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=198 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput file name=/u03/orabak/undotbs01.dbfchannel ORA_DISK_2: starting datafile conversioninput file name=/u03/orabak/system01.dbfconverted datafile=+DATA/RHNDB/DATAFILE/system.258.1006801423channel ORA_DISK_2: datafile conversion complete Elapsed time: 00:01:35converted datafile=+DATA/RHNDB/DATAFILE/undotbs1.257.1006801423channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:55Finished conversion at target at 28-APR-2019 1915 05VR 36yu-copy data files to ASM disk group RMAN > convert parallelism 3 2 > datafile'/ u03max orabakUBAK users01.dbf' format'+ data'3 > datafile'/ u03Universe orabakUniplex sysaux01.dbf' format'+ data'4 > datafile'/ u03 orabakUniple spw01.dbf' format'+ data' Starting conversion at target at 28-APR-2019 19:07:32using channel ORA_DISK_1using channel ORA_DISK_2allocated channel: ORA_DISK_3channel ORA_DISK_3: SID=2 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput file name=/u03/orabak/spw01.dbfchannel ORA_DISK_2: starting datafile conversioninput file name=/u03/orabak/sysaux01.dbfchannel ORA_DISK_3: starting datafile conversioninput file name=/u03/orabak/users01.dbfconverted datafile=+DATA/RHNDB/DATAFILE/users.261.1006801653channel ORA_DISK_3: datafile conversion complete Elapsed time: 00:00:03converted datafile=+DATA/RHNDB/DATAFILE/sysaux.260.1006801653channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:25converted datafile=+DATA/RHNDB/DATAFILE/spacewalk.259.1006801653channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:35Finished conversion at target at 28-APR-2019 19:09:08RMAN > exit8, create the control file on the target side

Use the following command on the source side to create the control file on the destination side:

SQL > alter database backup controlfile to trace resetlogs

The path of the generated trace file can be viewed through the alter log and then modified according to the actual situation. Execute it on the destination side after modification, as follows:

[oracle@db02] $sqlplus "/ as sysdba" SQL*Plus: Release 19.0.0.0.0-Production on Sun Apr 28 19:14:24 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0-ProductionVersion 19.3.0.0.0SQL > CREATE CONTROLFILE REUSE DATABASE "RHNDB" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 2920 7 LOGFILE 8 GROUP 1'+ DATA/rhndb/redo01.log' SIZE 50m BLOCKSIZE 512, 9 GROUP 2'+ DATA/rhndb/redo02.log' SIZE 50m BLOCKSIZE 512 10 GROUP 3'+ DATA/rhndb/redo03.log' SIZE 50m BLOCKSIZE 512 11-- STANDBY LOGFILE 12 DATAFILE 13'+ DATA/RHNDB/DATAFILE/system.258.1006801423', 14'+ DATA/RHNDB/DATAFILE/undotbs1.257.1006801423', 15'+ DATA/RHNDB/DATAFILE/users.261.1006801653', 16'+ DATA/RHNDB/DATAFILE/sysaux.260.1006801653', 17'+ DATA/RHNDB/DATAFILE/spacewalk.259.1006801653' 18 CHARACTER SET AL32UTF8 Control file created.

In addition, copy the password file of the source library to the specified directory of the target library:

[oracle@rhndb dbs] $scp orapwrhndb db02:/u01/app/oracle/product/19.0.0/db_1/dbs/9, open the database in resetlogs upgrade mode and create a temporary tablespace SQL > startup mountORACLE instance started.Total System Global Area 3221222464 bytesFixed Size 8901696 bytesVariable Size 671088640 bytesDatabase Buffers 2533359616 bytesRedo Buffers 7872512 bytesDatabase mounted.SQL > alter database open resetlogs upgrade;Database altered.SQL > alter tablespace temp add tempfile'+ data' size 50m autoextend on next 100m maxsize unlimited Tablespace altered.10, create SPFILESQL > create spfile='+data' from pfile;-- the newly created spfile name can be viewed in the asm disk group [oracle@rhndb dbs] $mv initrhndb.ora initrhndb.ora.old [oracle@db02 dbs] $echo 'SPFILE='+data/rhndb/parameterfile/spfile.267.1006905749'' > initrhndb.ora11, and execute upgrade

Use dbupgrade for upgrade operations:

[oracle@db02] $dbupgrade-u sys

During the upgrade process, you will encounter the following errors:

ORA-02290: check constraint (SYS.JAVA_DEV_DISABLED) violated

At the end of the dbupgrade session, use the following command to enable:

SQL > exec dbms_java_dev.enable

After this command is executed, the database is restarted to migrate mode, and then run the following command to complete the upgrade.

SQL > @? / rdbms/admin/utlirp.sqlSQL > shutdown immediateSQL > startupSQL > @? / rdbms/admin/utlrp.sql

If you also have java-related invalid objects, please refer to the official metalink (documentation ID 2262919.1).

Check the component information using the following command:

SQL > col comp_name for a40SQL > set wrap offSQL > set pagesize 999SQL > select comp_name,version, status from dba_registry 12. Register the database resource (optional) [oracle@db02 ~] $srvctl add database-db rhndb-oraclehome / u01/app/oracle/product/19.0.0/db_1-spfile'+ data/rhndb/parameterfile/spfile.267.1006814727'-pwfile / u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndb-role primary-dbname rhndb-diskgroup 'data Fra' [oracle@db02 ~] $srvctl config database-db rhndbDatabase unique name: rhndbDatabase name: rhndbOracle home: / u01/app/oracle/product/19.0.0/db_1Oracle user: oracleSpfile: + data/rhndb/parameterfile/spfile.267.1006814727Password file: / u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndbDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICDisk Groups: DATA FRAServices: OSDBA group: OSOPER group: Database instance: rhndb, what is the method of Oracle data migration? that's it. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.