sys@SYBO2SZ> startup mount force; --->切换数据库到mount状态 ORACLE instance started. Database mounted. sys@SYBO2SZ> @/users/robin/dba_scripts/custom/sql/transfer_db_files -->调用脚本进行数据及日志文件的迁移 Step 1, Coping file to destination from source ============================================ Step 2, updating files to control file ============================================ sys@SYBO2SZ> alter database open; -->切换数据库到open状态 Database altered. sys@SYBO2SZ> @dba_files_all_2.sql -->验证切换结果 Tablespace Name / File Class Filename File Size Auto ----------------------------- ---------------------------------------------------------------------- ---- GOEX_ACCOUNT_IDX /u02/database/SY5221BK/oradata/SY5221BK_account_idx.dbf 16,777,216 YES --.......... ................. TEMP /u02/database/SY5221BK/temp/tempSY5221BK.dbf 432,013,312 YES UNDOTBS1 /u02/database/SY5221BK/undo/undotbsSY5221BK.dbf 429,916,160 YES UNDOTBS2 /u02/database/SY5221BK/undo/undotbsSY5221BK2.dbf 314,572,800 YES [ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log3aSY5221BK.log 20,971,520 [ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log3bSY5221BK.log 20,971,520 [ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log4aSY5221BK.log 20,971,520 [ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log4bSY5221BK.log 20,971,520 ------------- sum 5,107,376,128 41 rows selected. --如果仅仅是迁移数据文件以及日志文件则上述步骤完成即可 --如果需要修改相关的参数文件以及迁移控制文件则继续下面的步骤 --由于控制文件的在mount状态下被校验,因此我们在nomount状态下来处理 sys@SYBO2SZ> shutdown immediate; sys@SYBO2SZ> startup nomount; -->修改参数文件之前先备份spfile sys@SYBO2SZ> create pfile='/users/oracle/OraHome10g/dbs/initSYBO2SZ_bak.ora' from spfile; File created. -->下面对相关的参数文件的路径进行修改,如果不需要修改参数,则跳过下面的步骤 sys@SYBO2SZ> show parameter dump NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /u02/database/SYBO2SZ/bdump core_dump_dest string /u02/database/SYBO2SZ/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /u02/database/SYBO2SZ/udump sys@SYBO2SZ> alter system set background_dump_dest='/u02/database/SY5221BK/bdump' scope=both sid='*'; System altered. sys@SYBO2SZ> alter system set core_dump_dest='/u02/database/SY5221BK/cdump' scope=both sid='*'; System altered. sys@SYBO2SZ> alter system set user_dump_dest='/u02/database/SY5221BK/udump' scope=both sid='*'; System altered. sys@SYBO2SZ> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u02/database/SYBO2SZ/flash_re covery_area db_recovery_file_dest_size big integer 1G sys@SYBO2SZ> alter system set db_recovery_file_dest='/u02/database/SY5221BK/flash_recovery_area' scope=both; System altered. sys@SYBO2SZ> show parameter LOG_ARCHIVE_DEST_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=/u02/database/SYBO2SZ /archive/ log_archive_dest_10 string sys@SYBO2SZ> alter system set log_archive_dest_1='LOCATION=/u02/database/SY5221BK/archive' scope=both; System altered. sys@SYBO2SZ> show parameter UTL_FILE_DIR NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir string /u02/database/SYBO2SZ/udump sys@SYBO2SZ> alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=both; alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=both * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified -->该参数不能修改内存值 sys@SYBO2SZ> alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=spfile;
System altered. -->下面对控制文件位置进行修改 sys@SYBO2SZ> show parameter control_f NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u02/database/SYBO2SZ/controlf /cntl1SYBO2SZ.ctl, /u02/databa se/SYBO2SZ/controlf/cntl2SYBO2 SZ.ctl, /u02/database/SYBO2SZ/ controlf/cntl3SYBO2SZ.ctl -->将控制文件复制到新位置 sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl1SY5221BK.ctl sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl2SY5221BK.ctl sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl3SY5221BK.ctl -->Author : Robinson Cheng -->Blog : http://blog.csdn.net/robinson_0612 -->通过修改control_files参数来修改控制文件位置 sys@SYBO2SZ> alter system set control_files='/u02/database/SY5221BK/controlf/cntl1SY5221BK.ctl', 2 '/u02/database/SY5221BK/controlf/cntl2SY5221BK.ctl','/u02/database/SY5221BK/controlf/cntl3SY5221BK.ctl' 3 scope=spfile; System altered. sys@SYBO2SZ> shutdown immediate; sys@SYBO2SZ> startup mount; SQL> show parameter control_f NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 7 control_files string /u02/database/SY5221BK/control f/cntl1SY5221BK.ctl, /u02/data base/SY5221BK/controlf/cntl2SY 5221BK.ctl, /u02/database/SY52 21BK/controlf/cntl3SY5221BK.ct l sys@SYBO2SZ> show parameter dump background_core_dump string partial background_dump_dest string /u02/database/SY5221BK/bdump core_dump_dest string /u02/database/SY5221BK/cdump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /u02/database/SY5221BK/udump sys@SYBO2SZ> alter database open; Database altered. |