Shulou(Shulou.com)06/01 Report--

Experimental subjects: two linux stand-alone oracle 12C databases (large version and small version are the same), data migration adopts cold backup. The path of the target machine is not the same as that of the source database, and the controlfile needs to be rebuilt.

Migration steps

First of all, turn off the applications that listen and disconnect with kill.

Ps-ef | grep LOCAL=NO | awk'{print ($2)}'| xargs kill-9

Create pfile

Create pfile='/tmp/inittest.ora1012' from spfile

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

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 shutting down the database, copy the parameter file, data file, log file, control file.

Shutdown immediate

Copy all the required files locally for better transfer.

Cp.

Copy the file to the target server.

Scp...

Modify the pfile information on the target server.

Source and sink

[oracle@test 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'

Rebuild 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, so we choose noresetlog mode because we have onlinelog

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 SIZE SIZE BLOCKSIZE 512

GROUP 2'/ u01 SIZE BLOCKSIZE 50m BLOCKSIZE 512

GROUP 3'/ u01 SIZE SIZE BLOCKSIZE 512

DATAFILE

'/ u01 apprenticeship oracleandoradataUniplicatedatafileUniplex system01.dbf'

'/ u01 apprenticespact oradataUniplicatedatafilehip sysaux01.dbf'

'/ u01 apprenticespact oradataUniplicatedatafileandundotbs01.dbf'

'/ u01 apprenticeship oracleandoradataUniplicatedatafileandusers01.dbf'

'/ u01 apprenticespact oracleandoradataUniplicatedatafilelapqwerty.dbf'

'/ u01 apprenticespact oracleandoradataUniplicatedatafileandtestbig.dbf'

'/ u01 apprenticeship oracleandoradataUniplicatedatafileanddataplains 01.dbf'

CHARACTER SET AL32UTF8

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL

ALTER DATABASE OPEN

ALTER TABLESPACE TEMP ADD TEMPFILE'/ u01 REUSE appUnitionoracle.oradataPlacement

ALTER TABLESPACE TEMP ADD TEMPFILE'/ u01 REUSE appUnitionoracleandoradata _ REUSE

ALTER TABLESPACE TEMP_ASYNC ADD TEMPFILE'/ u01 REUSE appActionoracleUniplicationoradata _ REUSE

After modification, start the database to 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

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.