Oracle Database 11g RAC 迁移至单机

小咪咪 2022-05-29 00:59 272阅读 0赞

RAC为生产环境、基于RMAN备份恢复将生产环境恢复到单机上、作为测试环境使用、  
单机环境安装oralce数据库软件、 不创建实例、

## 对于RAC： ##

查看DBID:

select dbid from v$database;

查看SCN:

select FIRST_CHANGE#,NEXT_CHANGE#,STATUS from v$log;

创建pfile：

create pfile from spfile；

rman备份：

rman target /

run {
    allocate channel c1 device type disk ;
    allocate channel c2 device type disk ;
    allocate channel c3 device type disk ;
    backup database format ='/bak/data_%s.bak' filesperset 3;
    backup current controlfile format ='/bak/conbak_%s.bak';
    backup spfile format ='/bak/spfile_%s.bak';
    sql 'alter system archive log current';
    crosscheck archivelog all;
    delete noprompt expired archivelog all ;
    backup archivelog  format ='/bak/arch_%s.bak'  ;   
    release channel c1 ;
    release channel c2 ;
    release channel c3 ;
    report obsolete;
    delete noprompt obsolete;
    crosscheck backup;
    delete noprompt expired backup;
    }

将pfile 与备份文传到单机上、（归档文件备份无效、 可选择将归档原文件传到单机上或弃用）

--------------------

## 对于单机： ##

更改单机 dbname：

host nid target=sys/oracle dbname=HESAUAT

创建备份目录、与RAC环境相同：

$ mkdir  /bak

创建单机环境使用的目录：

$ mkdir -p /u01/app/oracle/data/HESAUAT/adump
    $ chmod -R /u01/app/oracle/data/HESAUAT/adump
    $ mkdir -p /u01/app/oracle/data/HESAUAT/archive
    $ chmod -R /u01/app/oracle/data/HESAUAT/archive

rman 启动数据库：

startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initHESAUAT.ora';
    set DBID=3657334279

恢复控制文件并打开到mount状态下：

restore controlfile to '/u01/app/oracle/data/HESAUAT/control01.ctl' from  '/u01/app/oracle/bak/conbak_19.bak';
    alter database mount;

恢复数据文件：

CATALOG START WITH '/bak/';
    run{
         set newname for datafile 1 to '/u01/app/oracle/data/system01.dbf';
         set newname for datafile 2 to '/u01/app/oracle/data/sysaux01.dbf';
         set newname for datafile 3 to '/u01/app/oracle/data/UNDOTBS1.dbf';
         set newname for datafile 4 to '/u01/app/oracle/data/users01.dbf';
         set newname for datafile 5 to '/u01/app/oracle/data/UNDOTBS2.dbf';
         set newname for datafile 6 to '/u01/app/oracle/data/siebeldata.268.dbf';
         set newname for datafile 7 to '/u01/app/oracle/data/siebelindex.dbf';
         set newname for datafile 8 to '/u01/app/oracle/data/prod_mds.dbf';
         set newname for datafile 9 to '/u01/app/oracle/data/prod_biplatform.271.dbf';
         set newname for datafile 10 to '/u01/app/oracle/data/prd_biplatform.273.dbf';
         set newname for datafile 11 to '/u01/app/oracle/data/prd_mds.dbf';
         set newname for datafile 12 to '/u01/app/oracle/data/siebeldata.dbf';
         restore database;
         switch datafile 1;
         switch datafile 2;
         switch datafile 3;
         switch datafile 4;
         switch datafile 5;
         switch datafile 6;
         switch datafile 7;
         switch datafile 8;
         switch datafile 9;
         switch datafile 10;
         switch datafile 11;
         switch datafile 12;
       }

查看并修改归档文件：

select * from v$logfile;
    
    
    alter database rename file '+DATA/hesadb/onlinelog/group_2.262.914540939' to '/u01/app/oracle/data/HESAUAT/archive/group_2.262.914540939';
    alter database rename file '+CRS_RECOVERY/hesadb/onlinelog/group_2.258.914540939' to '/u01/app/oracle/data/HESAUAT/archive/group_2.258.914540939';
    
    
    alter database rename file '+DATA/hesadb/onlinelog/group_1.261.914540937' to '/u01/app/oracle/data/HESAUAT/archive/group_1.261.914540937';
    alter database rename file '+CRS_RECOVERY/hesadb/onlinelog/group_1.257.914540937' to '/u01/app/oracle/data/HESAUAT/archive/group_1.257.914540937';
    
    alter database rename file '+DATA/hesadb/onlinelog/group_3.265.914541049' to '/u01/app/oracle/data/HESAUAT/archive/group_3.265.914541049';
    alter database rename file '+CRS_RECOVERY/hesadb/onlinelog/group_3.259.914541049' to '/u01/app/oracle/data/HESAUAT/archive/group_3.259.914541049';
    
    alter database rename file '+DATA/hesadb/onlinelog/group_4.266.914541049' to '/u01/app/oracle/data/HESAUAT/archive/group_4.266.914541049';
    alter database rename file '+CRS_RECOVERY/hesadb/onlinelog/group_4.260.914541049' to '/u01/app/oracle/data/HESAUAT/archive/group_4.260.914541049';

创建新的redo文件：

alter database add logfile group 5'/u01/app/oracle/data/redo1.log' size 52M;
    alter database add logfile group 6'/u01/app/oracle/data/redo2.log' size 52M;
    alter database add logfile group 7'/u01/app/oracle/data/redo3.log' size 52M;

重建控制文件、并修改redo文件：

alter database backup controlfile to trace as '/u01/app/oracle/trace.bak';
    
    STARTUP NOMOUNT
    CREATE CONTROLFILE REUSE DATABASE "HESADB" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 192
        MAXLOGMEMBERS 3
        MAXDATAFILES 1024
        MAXINSTANCES 32
        MAXLOGHISTORY 2336
    LOGFILE
      GROUP 5 '/u01/app/oracle/data/redo1.log' SIZE 52M BLOCKSIZE 512,
      GROUP 6 '/u01/app/oracle/data/redo2.log' SIZE 52M BLOCKSIZE 512,
      GROUP 7 '/u01/app/oracle/data/redo3.log' SIZE 52M BLOCKSIZE 512
    -- STANDBY LOGFILE
    DATAFILE
      '/u01/app/oracle/data/system01.dbf',
      '/u01/app/oracle/data/sysaux01.dbf',
      '/u01/app/oracle/data/UNDOTBS1.dbf',
      '/u01/app/oracle/data/users01.dbf',
      '/u01/app/oracle/data/UNDOTBS2.dbf',
      '/u01/app/oracle/data/siebeldata.268.dbf',
      '/u01/app/oracle/data/siebelindex.dbf',
      '/u01/app/oracle/data/prod_mds.dbf',
      '/u01/app/oracle/data/prod_biplatform.271.dbf',
      '/u01/app/oracle/data/prd_biplatform.273.dbf',
      '/u01/app/oracle/data/prd_mds.dbf',
      '/u01/app/oracle/data/siebeldata.dbf'
    CHARACTER SET AL32UTF8
    ;

修改参数、强行打开数据库：

alter system set "_allow_resetlogs_corruption"=TRUE scope=spfile;
    alter system set "_no_recovery_through_resetlogs"=TRUE scope=spfile;

创建临时表空间：

create temporary tablespace TEMP1 TEMPFILE '/u01/app/oracle/data/TEMP1_01.dbf' size 1000M;
    alter database default temporary tablespace TEMP1;