ASM+RAC==>>单实例+文件系统迁移步骤

水深无声 2022-08-13 13:40 198阅读 0赞

1修改pfile文件（去掉集群专用，因原服务器有重名数据库，取别名'test'）  
2转储控制文件，数据库至mount  
3注册备份集目录  
4restore、recover database  
5修改redo日志路径  
6打开数据库

7善后（删除多余undo和redo、新建temp表空间）

1:修改pfile文件（去掉集群专用，因原服务器有重名数据库，取别名'test'）

#fyl1.__db_cache_size=109051904
    #fyl2.__db_cache_size=113246208
    #fyl1.__java_pool_size=4194304
    #fyl2.__java_pool_size=4194304
    #fyl1.__large_pool_size=4194304
    #fyl2.__large_pool_size=4194304
    #fyl1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    #fyl2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    #fyl1.__pga_aggregate_target=209715200
    #fyl2.__pga_aggregate_target=209715200
    #fyl1.__sga_target=314572800
    #fyl2.__sga_target=314572800
    #fyl1.__shared_io_pool_size=0
    #fyl2.__shared_io_pool_size=0
    #fyl1.__shared_pool_size=188743680
    #fyl2.__shared_pool_size=184549376
    #fyl1.__streams_pool_size=0
    #fyl2.__streams_pool_size=0
    *.audit_file_dest='/u01/app/oracle/admin/fyl/adump'
    *.audit_trail='db'
    #*.cluster_database=true
    *.compatible='11.2.0.0.0'
    *.control_files='/u01/app/oracle/oradata/test/control01.ctl'
    *.db_block_size=8192
    #*.db_create_file_dest='+DATE1'
    *.db_domain=''
    *.db_name='fyl'
    db_unique_name='test'
    *.diagnostic_dest='/u01/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=fylXDB)'
    #fyl2.instance_number=2
    #fyl1.instance_number=1
    #*.local_listener=''
    #rac1.local_listener='rac'
    #rac2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1522))))'
    *.log_archive_dest_1='location=/u02/arch'
    #*.log_archive_dest_2='location=+DATE1'
    *.log_archive_format='fyl%t_%s_%r.log'
    *.memory_target=524288000
    *.nls_language='SIMPLIFIED CHINESE'
    *.nls_territory='CHINA'
    *.open_cursors=300

2转储控制文件，数据库至mount

[oracle@fyl dbs]$ export ORACLE_SID=test
    [oracle@fyl dbs]$ rman target /
    Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 28 19:23:05 2014
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database (not started)
    
    RMAN> startup nomount;
    Oracle instance started
    
    Total System Global Area     523108352 bytes
    Fixed Size                     1337632 bytes
    Variable Size                314574560 bytes
    Database Buffers             201326592 bytes
    Redo Buffers                   5869568 bytes
    
    RMAN> restore controlfile from '/oracle/rman_fyl/rman_rac/fyl1_1_6.ctl';
    
    Starting restore at 28-OCT-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK
    
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    output file name=/u01/app/oracle/oradata/test/control01.ctl
    Finished restore at 28-OCT-14
    
    RMAN> startup mount;
    
    database is already started
    database mounted
    released channel: ORA_DISK_1

3 注册备份集目录

RMAN> catalog start with '/oracle/rman_fyl/rman_rac';
    
    searching for all files that match the pattern /oracle/rman_fyl/rman_rac
    List of Files Unknown to the Database
    =====================================
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_5_05pl78qj.arc
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_3_03pl78mf.dbf
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_6.ctl
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_4_04pl78pk.dbf
    File Name: /oracle/rman_fyl/rman_rac/inittext.ora
    
    Do you really want to catalog the above files (enter YES or NO)? y
    cataloging files...
    cataloging done
    List of Cataloged Files
    =======================
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_5_05pl78qj.arc
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_3_03pl78mf.dbf
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_6.ctl
    File Name: /oracle/rman_fyl/rman_rac/fyl1_1_4_04pl78pk.dbf
    List of Files Which Where Not Cataloged
    =======================================
    File Name: /oracle/rman_fyl/rman_rac/inittext.ora
      RMAN-07517: Reason: The file header is corrupted

4restore、recover database

RMAN> report schema;
    
    RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
    Report of database schema for database with db_unique_name TEST
    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    0        SYSTEM               ***     +DATE1/fyl/datafile/system.259.858119563
    2    0        SYSAUX               ***     +DATE1/fyl/datafile/sysaux.260.858119595
    3    0        UNDOTBS1             ***     +DATE1/fyl/datafile/undotbs1.261.858119615
    4    0        UNDOTBS2             ***     +DATE1/fyl/datafile/undotbs2.263.858119637
    5    0        USERS                ***     +DATE1/fyl/datafile/users.264.858119641
    List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    0        TEMP                 32767       +DATE1/fyl/tempfile/temp.262.858119621
    
    RMAN> run {
    2> set newname for datafile '+DATE1/fyl/datafile/system.259.858119563' to '/u01/app/oracle/oradata/test/system.dbf';
    3> set newname for datafile '+DATE1/fyl/datafile/sysaux.260.858119595' to '/u01/app/oracle/oradata/test/sysaux.dbf';
    4> set newname for datafile '+DATE1/fyl/datafile/undotbs1.261.858119615' to '/u01/app/oracle/oradata/test/undotbs1.dbf';
    5> set newname for datafile '+DATE1/fyl/datafile/undotbs2.263.858119637' to '/u01/app/oracle/oradata/test/undotbs2.dbf';
    6> set newname for datafile '+DATE1/fyl/datafile/users.264.858119641' to '/u01/app/oracle/oradata/test/users.dbf';
    7> restore database;
    8> switch datafile all;
    9> recover database;
    10> }
    
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    executing command: SET NEWNAME
    Starting restore at 28-OCT-14
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test/system.dbf
    channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux.dbf
    channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs1.dbf
    channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/undotbs2.dbf
    channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test/users.dbf
    channel ORA_DISK_1: reading from backup piece /u02/rman/20141016_0/fyl1_1_3_03pl78mf.dbf
    channel ORA_DISK_1: errors found reading piece handle=/u02/rman/20141016_0/fyl1_1_3_03pl78mf.dbf
    channel ORA_DISK_1: failover to piece handle=/oracle/rman_fyl/rman_rac/fyl1_1_3_03pl78mf.dbf tag=TAG20141016T154623
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
    Finished restore at 28-OCT-14
    
    datafile 1 switched to datafile copy
    input datafile copy RECID=6 STAMP=862169998 file name=/u01/app/oracle/oradata/test/system.dbf
    datafile 2 switched to datafile copy
    input datafile copy RECID=7 STAMP=862169998 file name=/u01/app/oracle/oradata/test/sysaux.dbf
    datafile 3 switched to datafile copy
    input datafile copy RECID=8 STAMP=862169998 file name=/u01/app/oracle/oradata/test/undotbs1.dbf
    datafile 4 switched to datafile copy
    input datafile copy RECID=9 STAMP=862169998 file name=/u01/app/oracle/oradata/test/undotbs2.dbf
    datafile 5 switched to datafile copy
    input datafile copy RECID=10 STAMP=862169998 file name=/u01/app/oracle/oradata/test/users.dbf
    
    Starting recover at 28-OCT-14
    using channel ORA_DISK_1
    starting media recovery
    
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=2 sequence=15
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=53
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=54
    channel ORA_DISK_1: restoring archived log
    archived log thread=2 sequence=16
    channel ORA_DISK_1: reading from backup piece /u02/rman/20141016_0/fyl1_1_5_05pl78qj.arc
    channel ORA_DISK_1: errors found reading piece handle=/u02/rman/20141016_0/fyl1_1_5_05pl78qj.arc
    channel ORA_DISK_1: failover to piece handle=/oracle/rman_fyl/rman_rac/fyl1_1_5_05pl78qj.arc tag=TAG20141016T154834
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archfyl1_53_858119547.log thread=1 sequence=53
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archfyl2_15_858119547.log thread=2 sequence=15
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archfyl1_54_858119547.log thread=1 sequence=54
    archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/archfyl2_16_858119547.log thread=2 sequence=16
    unable to find archived log
    archived log thread=1 sequence=55
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 10/28/2014 19:40:06
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 55 and starting SCN of 932155

5修改redo日志路径

SYS@ test>select * from v$logfile;
        GROUP# STATUS          TYPE            MEMBER                                         IS_RECOVE
    ---------- --------------- --------------- ---------------------------------------------- ---------
             1                 ONLINE          +DATE1/fyl/onlinelog/group_1.257.858119555     NO
             2                 ONLINE          +DATE1/fyl/onlinelog/group_2.258.858119559     NO
             3                 ONLINE          +DATE1/fyl/onlinelog/group_3.265.858121703     NO
             4                 ONLINE          +DATE1/fyl/onlinelog/group_4.266.858121705     NO
             
    SYS@ test>alter database rename file '+DATE1/fyl/onlinelog/group_1.257.858119555' to '/u01/app/oracle/oradata/test/redo01.log';  
    alter database rename file '+DATE1/fyl/onlinelog/group_2.258.858119559' to '/u01/app/oracle/oradata/test/redo02.log';
    alter database rename file '+DATE1/fyl/onlinelog/group_3.265.858121703' to '/u01/app/oracle/oradata/test/redo03.log';
    alter database rename file '+DATE1/fyl/onlinelog/group_4.266.858121705' to '/u01/app/oracle/oradata/test/redo04.log';
    Database altered.
    
    SYS@ test>select * from v$logfile;
        GROUP# STATUS       TYPE              MEMBER                                         IS_RECOVE
    ---------- ------------ ----------------- ---------------------------------------------- ---------
             1              ONLINE            /u01/app/oracle/oradata/test/redo01.log        NO
             2              ONLINE            /u01/app/oracle/oradata/test/redo02.log        NO
             3              ONLINE            /u01/app/oracle/oradata/test/redo03.log        NO
             4              ONLINE            /u01/app/oracle/oradata/test/redo04.log        NO

6打开数据库  
SYS@ test>alter database open resetlogs;  
Database altered.

7善后（删除多余undo和redo、新建temp表空间）

SYS@ test>select GROUP# ,THREAD#,SEQUENCE#, MEMBERS,STATUS from v$log;
        GROUP#    THREAD#  SEQUENCE#    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ------------------------------------------------
             1          1          1          1 CURRENT
             2          1          0          1 UNUSED
             3          2          1          1 ACTIVE
             4          2          0          1 UNUSED
    SYS@ test>select thread#,status,enabled from v$thread;
       THREAD# STATUS             ENABLED
    ---------- ------------------ ------------------------
             1 OPEN               PUBLIC
             2 CLOSED             PUBLIC
             
    SYS@ test>alter database disable thread 2;
    Database altered.
    
    SYS@ test>alter database drop logfile group 3;
    Database altered.
    
    SYS@ test>alter database drop logfile group 4;
    Database altered.
    
    SYS@ test>select GROUP# ,THREAD#,SEQUENCE#, MEMBERS,STATUS from v$log;
        GROUP#    THREAD#  SEQUENCE#    MEMBERS STATUS
    ---------- ---------- ---------- ---------- ------------------------------------------------
             1          1          1          1 CURRENT
             2          1          0          1 UNUSED
             
    SYS@ test>drop tablespace undotbs2 including contents and datafiles;
    Tablespace dropped.
    
    SYS@ test>select * from dba_temp_files;
    select * from dba_temp_files
                  *
    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    ORA-01110: data file 201: '+DATE1/fyl/tempfile/temp.262.858119621'
    
    SYS@ test>create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/test/temp1.dbf' size 10M;
    Tablespace created.
    SYS@ test>alter database default temporary tablespace temp1;
    Database altered.
    SYS@ test>drop tablespace temp including contents and datafiles;
    Tablespace dropped.
    
    SYS@ test>select FILE_NAME,FILE_ID ,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE from dba_temp_files;
    
    FILE_NAME                                             FILE_ID TABLESPACE STATUS                AUTOEXTEN
    -------------------------------------------------- ---------- ---------- --------------------- ---------
    /u01/app/oracle/oradata/test/temp1.dbf                      2 TEMP1      ONLINE                NO