ORACLE Local Cold migration

Requirements: Migrate Oracle database data files, redo files, control files to other local directories.

1. Test environment:

Operating system Redhat 6.3, database Oracle 11.2.0.1.0

[Email protected] ~]# uname-Alinux Dbtest12.6. +-279. el6.x86_64 #1SMP Wed June -  -: -: $EDT -x86_64 x86_64 x86_64 gnu/Linux

SQL>Select* fromv$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release11.2.0.1.0-64bit PRODUCTIONPL/sql Release11.2.0.1.0-Productioncore11.2.0.1.0Productiontns forLinux:version11.2.0.1.0-Productionnlsrtl Version11.2.0.1.0-Production

Source data File Location:/home/data/oracle/dbtest/

Destination data File Location:/oracle_data/

2. Turn off database snooping and prohibit external access to the database

-bash-4.1$ lsnrctl Stoplsnrctl forLinux:version11.2.0.1.0-Production on --dec- the  -: A: toCopyright (c)1991, the, Oracle. All rights reserved. Connecting to (DESCRIPTION= (address= (protocol=tcp) (host=127.0.0.1) (port=1521)) (Connect_data= (server=dedicated) (service_name=dbtest)))  The command completed successfully

3. Close the Oracle Database

Sql> shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down.

4. Backup Pfile,spfile, control files (backup at least one)

-bash-4.1$ cd/$ORACLE _home/dbs-bash-4.1$ cp initdbtest.ora Initdbtest.ora. 141218-bash-4.1$ cp spfiledbtest.ora  Spfiledbtest.ora.  141218-bash-4.1$ cd/home/data/oracle/dbtest/-bash-4.1$ cp control01.ctl Control01.ctl. 141218

5. Update Pfile, create Pfile from SPFile

sql> Create pfile='$ORACLE _home/dbs/initdbtest.ora' from spfile= ' $ORACLE _home/dbs/spfiledbtest.ora ';

Note: The default location can be created directly using create Pfile from SPFile, and after creation, check the timestamp to confirm.

6. Modify the location of the control files in the Pfile

Change the/home/data/oracle/dbtest/control01.ctl to/oracle_data/control01.ctl

-bash-4.1$ cd/$ORACLE _home/dbs/-bash-4.1$ lshc_dbtest.dat initdbtest.ora Initdbtest.ora.141218Init.ora lkdbtest orapwdbtest Spfiledbtest.ora Spfiledbtest.ora.141218-bash-4.1$ Cat Initdbtest.ora |grep Control*.control_files='/home/data/oracle/dbtest/control01.ctl','/home/app/ora11g/flash_recovery_area/dbtest/control02.ctl'Edit the file using VI-bash-4.1$ Cat Initdbtest.ora |grep Control*.control_files='/oracle_data/control01.ctl','/home/app/ora11g/flash_recovery_area/dbtest/control02.ctl'

Note: If there are multiple changes, the method is the same.

7. Copy files from the source data directory, including control files, data files, and redo files

-bash-4.1$ cp/home/data/oracle/dbtest/*  

8. Use Pfile to start the database to Mount state

sql> startup pfile='$ORACLE _home/dbs/initdbtest.ora'1603411968 bytesfixed Size                   2213776 bytesvariable Size             1241516144 bytesdatabase buffers           352321536 Bytesredo buffers                 7360512 bytesdatabase mounted.

9. Rename the data file, the location of the redo file

' /home/data/oracle/dbtest/redo01.log ' ' /oracle_data/redo01.log ' ;D atabase altered. SQL'/home/data/oracle/dbtest/system01.dbf'/oracle_data/ SYSTEM01.DBF';D atabase altered ....

10. Open the Database

sql> ALTER DATABASE open;database altered.

11. Check the data file, redo, etc. file location is correct

Sql>Selectfile_name fromDba_data_files; file_name--------------------------------------------------------------------------------/oracle_data/users01.dbf/oracle_data/undotbs01.dbf/oracle_data/sysaux01.dbf/oracle_data/system01.dbf/oracle_data/laputa_dat.dbf/oracle_data/laputa_idx_01.dbf6rows selected. SQL>Selectfile_name fromDba_temp_files; file_name--------------------------------------------------------------------------------/oracle_data/Temp01.dbfsql>SelectName fromV$controlfile;name--------------------------------------------------------------------------------/oracle_data/Control01.ctl/home/app/ora11g/flash_recovery_area/dbtest/Control02.ctlsql>SelectMember fromV$logfile; MEMBER--------------------------------------------------------------------------------/oracle_data/Redo03.log/oracle_data/Redo02.log/oracle_data/redo01.log

12. Synchronizing SPFile to a new file (simplified notation)

 from Pfile;file created.

13. Reboot the database to confirm that everything is fine.

Sql> shutdown immediatedatabase closed. Database dismounted. ORACLE instance shut down. SQL>1603411968  bytesfixed size                  2213776  bytesvariable size             1241516144  bytesdatabase buffers          352321536  Bytesredo buffers                 7360512  bytesdatabase mounted. Database opened. SQL

14. Turn on database monitoring

-bash-4.1$ lsnrctl Start

Note the problem:

1. In the rename file, if the file is more involved, it is recommended to put the statement in a SQL file, and then executed in Sqlplus, otherwise it may cause the statement in the middle break (a statement by the command line as 2 lines, will be error). If a statement breaks, and the statement is executed in the SQL file again, the repeated execution of these rename statements will give an error, but the final result is not affected.

2. The migration redo file can be executed online, with the addition of Redo group, to remove the redo group approach.

3. Non-system tablespaces can be migrated without shutting down the database, offline the tablespace, moving the corresponding data file, rename the data file, and online table space.

ORACLE Local Cold migration