Data migration in ORACLE 12C Cold backup Mode

Experimental object: Two Linux stand-alone Oracle 12C databases (both large and small versions are consistent), data migration takes a cold backup way. The path of the target machine is inconsistent with the source library and needs to be rebuilt controlfile.



Migration steps

First turn off monitoring, and kill the connected app
Ps-ef | grep Local=no | awk ' {print ($)} ' | Xargs kill-9



Create Pfile
Create pfile= '/tmp/inittest.ora1012 ' from SPFile;


View the path of the data file, log file, log file path

Sql> Set Line 200
Sql> Col file_name for A80

Sql> select Tablespace_name,file_name from Dba_data_files;

Tablespace_name file_name
------------------------------ --------------------------------------------------------------------------------
System/u01/app/oracle/oradata/test/system01.dbf
Sysaux/u01/app/oracle/oradata/test/sysaux01.dbf
Undotbs1/u01/app/oracle/oradata/test/undotbs01.dbf
Users/u01/app/oracle/oradata/test/users01.dbf
Qwerty/u01/app/oracle/oradata/test/qwerty.dbf
Testbig/u01/app/oracle/oradata/test/testbig.dbf
Data/u01/app/oracle/oradata/test/data_01.dbf


Sql> select Tablespace_name,file_name from Dba_temp_files;

Tablespace_name file_name
------------------------------ --------------------------------------------------------------------------------
Temp/u01/app/oracle/oradata/test/temp01.dbf
Temp_async/u01/app/oracle/oradata/test/temp_async_01.dbf



Sql> Set Line 200
Sql> Col MEMBER for A80
Sql> select Group#,member from V$logfile;

group# MEMBER
---------- --------------------------------------------------------------------------------
1/u01/app/oracle/oradata/test/redo01.log
2/u01/app/oracle/oradata/test/redo02.log
3/u01/app/oracle/oradata/test/redo03.log




Sql> Show Parameter Control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Control_files string/u01/app/oracle/oradata/test/c
Ontrol01.ctl,/u01/app/oracle/
Fast_recovery_area/test/contro
L02.ctl





After closing the database, copy the parameter files, data files, log files, control files.
Shutdown immediate


Copy all the required files locally for better transmission.
Cp......



Copy the file to the destination server.
Scp...



Modify the Pfile information on the destination server.

Source Library
[Email protected] dbs]$ VI Inittest.ora
Test.__data_transfer_cache_size=0
test.__db_cache_size=339738624
test.__java_pool_size=4194304
test.__large_pool_size=8388608
Test.__oracle_base= '/u01/app/oracle ' #ORACLE_BASE set from environment
test.__pga_aggregate_target=293601280
test.__sga_target=545259520
test.__shared_io_pool_size=16777216
test.__shared_pool_size=167772160
Test.__streams_pool_size=0
*.audit_file_dest= '/u01/app/oracle/admin/test/adump '
*.audit_trail= ' DB '
*.compatible= ' 12.1.0.2.0 '
*.control_files= '/u01/app/oracle/oradata/test/control01.ctl '
*.db_block_size=8192
*.db_domain= "
*.db_name= ' Test '
*.db_recovery_file_dest= '/u01/app/oracle/fast_recovery_area '
*.db_recovery_file_dest_size=4815m
*.diagnostic_dest= '/u01/app/oracle '
*.dispatchers= ' (protocol=tcp) (SERVICE=TESTXDB) '
*.log_archive_format= '%t_%s_%r.dbf '
*.memory_target=800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile= ' EXCLUSIVE '
*.undo_tablespace= ' UNDOTBS1 '





The following actions are performed on the target library:

Modified the path of the control file
*.control_files= '/u01/app/oracle/oradata/test/controlfile/control01.ctl '



Rebuilding the control file
Startup Mount

ALTER DATABASE backup Controlfile to trace as '/tmp/crontol_trace ';

Cat/tmp/crontol_trace | Grep-v ^-| Grep-v ^$ >/tmp/ctl.sql


Modify the path in the control file because we have onlinelog, so choose Noresetlog mode
Vi/tmp/ctl.sql


STARTUP Nomount
CREATE controlfile Reuse DATABASE "TEST" Noresetlogs ARCHIVELOG
Maxlogfiles 16
Maxlogmembers 3
Maxdatafiles 100
Maxinstances 8
Maxloghistory 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/test/onlinelog/redo01.log ' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/test/onlinelog/redo02.log ' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/test/onlinelog/redo03.log ' SIZE 50M BLOCKSIZE 512
DataFile
'/u01/app/oracle/oradata/test/datafile/system01.dbf ',
'/u01/app/oracle/oradata/test/datafile/sysaux01.dbf ',
'/u01/app/oracle/oradata/test/datafile/undotbs01.dbf ',
'/u01/app/oracle/oradata/test/datafile/users01.dbf ',
'/u01/app/oracle/oradata/test/datafile/qwerty.dbf ',
'/u01/app/oracle/oradata/test/datafile/testbig.dbf ',
'/U01/APP/ORACLE/ORADATA/TEST/DATAFILE/DATA_01.DBF '
CHARACTER SET Al32utf8
;
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG all;
ALTER DATABASE OPEN;
ALTER tablespace TEMP ADD tempfile '/u01/app/oracle/oradata/test/datafile/temp01.dbf ' reuse;
ALTER tablespace TEMP ADD tempfile '/u01/app/oracle/oradata/test/datafile/temp02.dbf ' reuse;
ALTER tablespace temp_async ADD tempfile '/u01/app/oracle/oradata/test/datafile/temp_async_01.dbf ' REUSE;


After the modification is complete, start the database to the Nomount state
Shutdown immediate

Startup Nomout;

Run the SQL statement that rebuilds the control file
@/tmp/ctl.sql

Sql> select status from V$instance;

STATUS
------------
OPEN

At this point, the migration of the database has been completed.

This article is from the "SYSDBA" blog, make sure to keep this source http://sysdba.blog.51cto.com/10492366/1702149

Data migration in ORACLE 12C Cold backup Mode